-- Start of IDENTITY Tables--
CREATE TABLE IDN_BASE_TABLE (
            PRODUCT_NAME VARCHAR (20) NOT NULL,
            PRIMARY KEY (PRODUCT_NAME))
/
INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server')
/
CREATE TABLE IDN_OAUTH_CONSUMER_APPS (
            ID INTEGER NOT NULL,
            CONSUMER_KEY VARCHAR (255) NOT NULL,
            CONSUMER_SECRET VARCHAR (2048),
            USERNAME VARCHAR (255),
            TENANT_ID INTEGER DEFAULT 0,
            USER_DOMAIN VARCHAR(50),
            APP_NAME VARCHAR (255),
            OAUTH_VERSION VARCHAR (128),
            CALLBACK_URL VARCHAR (2048),
            GRANT_TYPES VARCHAR (1024),
            PKCE_MANDATORY CHAR(1) DEFAULT '0',
            PKCE_SUPPORT_PLAIN CHAR(1) DEFAULT '0',
            APP_STATE VARCHAR (25) DEFAULT 'ACTIVE',
            USER_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
            APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
            REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600,
            ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
            CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
            PRIMARY KEY (ID))
/
CREATE SEQUENCE IDN_OAUTH_CONSUMER_APPS_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
  /
CREATE TRIGGER IDN_OAUTH_CONSUMER_APPS_TRIGGER NO CASCADE BEFORE INSERT ON IDN_OAUTH_CONSUMER_APPS
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID)
    = (NEXTVAL FOR IDN_OAUTH_CONSUMER_APPS_SEQUENCE);
  END
/
CREATE TABLE IDN_OAUTH2_SCOPE_VALIDATORS (
	APP_ID INTEGER NOT NULL,
	SCOPE_VALIDATOR VARCHAR (128) NOT NULL,
	PRIMARY KEY (APP_ID, SCOPE_VALIDATOR),
	FOREIGN KEY (APP_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
)
/
CREATE TABLE IDN_OAUTH1A_REQUEST_TOKEN (
            REQUEST_TOKEN VARCHAR (512) NOT NULL,
            REQUEST_TOKEN_SECRET VARCHAR (512),
            CONSUMER_KEY_ID INTEGER,
            CALLBACK_URL VARCHAR (2048),
            SCOPE VARCHAR(2048),
            AUTHORIZED VARCHAR (128),
            OAUTH_VERIFIER VARCHAR (512),
            AUTHZ_USER VARCHAR (512),
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (REQUEST_TOKEN),
            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE)
/
CREATE TABLE IDN_OAUTH1A_ACCESS_TOKEN (
            ACCESS_TOKEN VARCHAR (512) NOT NULL,
            ACCESS_TOKEN_SECRET VARCHAR (512),
            CONSUMER_KEY_ID INTEGER,
            SCOPE VARCHAR(2048),
            AUTHZ_USER VARCHAR (512),
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (ACCESS_TOKEN),
            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE)
/
CREATE BUFFERPOOL BP32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32K
/
CREATE LARGE TABLESPACE TS32K PAGESIZE 32K MANAGED by AUTOMATIC
STORAGE BUFFERPOOL BP32K
/
CREATE SYSTEM TEMPORARY TABLESPACE STS32K PAGESIZE 32K MANAGED by AUTOMATIC
STORAGE BUFFERPOOL BP32K
/
CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN (
            TOKEN_ID VARCHAR (255) NOT NULL,
            ACCESS_TOKEN VARCHAR (2048) NOT NULL,
            REFRESH_TOKEN VARCHAR (2048),
            CONSUMER_KEY_ID INTEGER NOT NULL,
            AUTHZ_USER VARCHAR (100) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            USER_DOMAIN VARCHAR (50) NOT NULL,
            USER_TYPE VARCHAR (25) NOT NULL,
            GRANT_TYPE VARCHAR (50),
            TIME_CREATED TIMESTAMP,
            REFRESH_TOKEN_TIME_CREATED TIMESTAMP,
            VALIDITY_PERIOD BIGINT,
            REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
            TOKEN_SCOPE_HASH VARCHAR (32) NOT NULL,
            TOKEN_STATE VARCHAR (25) DEFAULT 'ACTIVE' NOT NULL,
            TOKEN_STATE_ID VARCHAR (128) DEFAULT 'NONE' NOT NULL,
            SUBJECT_IDENTIFIER VARCHAR(255),
            ACCESS_TOKEN_HASH VARCHAR (512),
            REFRESH_TOKEN_HASH VARCHAR (512),
            IDP_ID INTEGER DEFAULT -1 NOT NULL,
            TOKEN_BINDING_REF VARCHAR (32) DEFAULT 'NONE' NOT NULL,
            CONSENTED_TOKEN VARCHAR(6),
            PRIMARY KEY (TOKEN_ID),
            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
            CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,
                                 TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF)) INDEX IN TS32K
/

CREATE TABLE IDN_OAUTH2_TOKEN_BINDING (
            TOKEN_ID VARCHAR (255) NOT NULL,
            TOKEN_BINDING_TYPE VARCHAR (32) NOT NULL,
            TOKEN_BINDING_REF VARCHAR (32) NOT NULL,
            TOKEN_BINDING_VALUE VARCHAR (1024) NOT NULL,
            TENANT_ID INTEGER DEFAULT -1,
            UNIQUE (TOKEN_ID,TOKEN_BINDING_TYPE,TOKEN_BINDING_VALUE),
            FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE) INDEX IN TS32K
/

CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_AUDIT (
            ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            TOKEN_ID VARCHAR (255),
            ACCESS_TOKEN VARCHAR(2048),
            REFRESH_TOKEN VARCHAR(2048),
            CONSUMER_KEY_ID INTEGER,
            AUTHZ_USER VARCHAR (100),
            TENANT_ID INTEGER,
            USER_DOMAIN VARCHAR(50),
            USER_TYPE VARCHAR (25),
            GRANT_TYPE VARCHAR (50),
            TIME_CREATED TIMESTAMP NULL,
            REFRESH_TOKEN_TIME_CREATED TIMESTAMP NULL,
            VALIDITY_PERIOD BIGINT,
            REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
            TOKEN_SCOPE_HASH VARCHAR(32),
            TOKEN_STATE VARCHAR(25),
            TOKEN_STATE_ID VARCHAR (128) ,
            SUBJECT_IDENTIFIER VARCHAR(255),
            ACCESS_TOKEN_HASH VARCHAR(512),
            REFRESH_TOKEN_HASH VARCHAR(512),
            INVALIDATED_TIME TIMESTAMP NULL,
            IDP_ID INTEGER DEFAULT -1 NOT NULL,
            PRIMARY KEY(ID))

/
CREATE TABLE IDN_OAUTH2_AUTHORIZATION_CODE (
            CODE_ID VARCHAR (255) NOT NULL,
            AUTHORIZATION_CODE VARCHAR (2048) NOT NULL,
            CONSUMER_KEY_ID INTEGER,
            CALLBACK_URL VARCHAR (2048),
            SCOPE VARCHAR(2048),
            AUTHZ_USER VARCHAR (100) NOT NULL,
            TENANT_ID INTEGER,
            USER_DOMAIN VARCHAR (50) NOT NULL,
            TIME_CREATED TIMESTAMP,
            VALIDITY_PERIOD BIGINT,
            STATE VARCHAR (25) DEFAULT 'ACTIVE',
            TOKEN_ID VARCHAR(255),
            SUBJECT_IDENTIFIER VARCHAR(255),
            PKCE_CODE_CHALLENGE VARCHAR(255),
            PKCE_CODE_CHALLENGE_METHOD VARCHAR(128),
            AUTHORIZATION_CODE_HASH VARCHAR (512),
            IDP_ID INTEGER DEFAULT -1 NOT NULL,
            PRIMARY KEY (CODE_ID),
            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE)
            INDEX IN TS32K
/
CREATE TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE(
             CODE_ID   VARCHAR(255) NOT NULL,
             SCOPE     VARCHAR(60) NOT NULL,
             TENANT_ID INTEGER DEFAULT -1,
             PRIMARY KEY (CODE_ID, SCOPE),
             FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE (CODE_ID) ON DELETE CASCADE)
/

CREATE TABLE IDN_OAUTH2_DEVICE_FLOW (
            CODE_ID VARCHAR(255) NOT NULL,
            DEVICE_CODE VARCHAR(255) NOT NULL,
            USER_CODE VARCHAR(25) NOT NULL,
            QUANTIFIER INTEGER NOT NULL DEFAULT 0,
            CONSUMER_KEY_ID INTEGER,
            LAST_POLL_TIME TIMESTAMP NOT NULL,
            EXPIRY_TIME TIMESTAMP NOT NULL,
            TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            POLL_TIME BIGINT,
            STATUS VARCHAR (25) DEFAULT 'PENDING',
            AUTHZ_USER VARCHAR (100),
            TENANT_ID INTEGER,
            USER_DOMAIN VARCHAR(50),
            IDP_ID INTEGER,
            PRIMARY KEY (DEVICE_CODE),
            UNIQUE (CODE_ID),
            CONSTRAINT USRCDE_QNTFR_CONSTRAINT UNIQUE (USER_CODE, QUANTIFIER),
            FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE)
/

CREATE TABLE IDN_OAUTH2_DEVICE_FLOW_SCOPES (
            ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            SCOPE_ID VARCHAR(255),
            SCOPE VARCHAR(255),
            PRIMARY KEY (ID),
            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_DEVICE_FLOW(CODE_ID) ON DELETE CASCADE)
/

CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_SCOPE (
            TOKEN_ID VARCHAR (255) NOT NULL,
            TOKEN_SCOPE VARCHAR (100) NOT NULL,
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (TOKEN_ID, TOKEN_SCOPE),
            FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE)
/
CREATE TABLE IDN_OAUTH2_SCOPE (
            SCOPE_ID INTEGER NOT NULL,
            NAME VARCHAR(255) NOT NULL,
            DISPLAY_NAME VARCHAR(255) NOT NULL,
            DESCRIPTION VARCHAR(512),
            TENANT_ID INTEGER NOT NULL DEFAULT -1,
            SCOPE_TYPE VARCHAR(255) NOT NULL,
            PRIMARY KEY (SCOPE_ID),
            UNIQUE (NAME, TENANT_ID))
/
CREATE SEQUENCE IDN_OAUTH2_SCOPE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OAUTH2_SCOPE_TRIGGER NO CASCADE BEFORE INSERT ON IDN_OAUTH2_SCOPE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.SCOPE_ID)
       = (NEXTVAL FOR IDN_OAUTH2_SCOPE_SEQUENCE);

END
/
CREATE TABLE IDN_OAUTH2_SCOPE_BINDING (
            ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            SCOPE_ID INTEGER NOT NULL,
            SCOPE_BINDING VARCHAR(255) NOT NULL,
            BINDING_TYPE  VARCHAR(255) NOT NULL,
            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
            UNIQUE (SCOPE_ID, SCOPE_BINDING, BINDING_TYPE),
            PRIMARY KEY (ID))
/
CREATE TABLE IDN_OAUTH2_RESOURCE_SCOPE (
            RESOURCE_PATH VARCHAR (255) NOT NULL,
            SCOPE_ID INTEGER NOT NULL,
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (RESOURCE_PATH),
            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE)
/
CREATE TABLE IDN_SCIM_GROUP (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            ROLE_NAME VARCHAR(255) NOT NULL,
            ATTR_NAME VARCHAR(1024) NOT NULL,
            ATTR_VALUE VARCHAR(1024),
            CONSTRAINT IDX_IDN_SCIM_GROUP_TI_RN_AN UNIQUE(TENANT_ID,ROLE_NAME,ATTR_NAME),
            PRIMARY KEY (ID)) IN TS32K
/

CREATE SEQUENCE IDN_SCIM_GROUP_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_SCIM_GROUP_TRIGGER NO CASCADE BEFORE INSERT ON IDN_SCIM_GROUP
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR IDN_SCIM_GROUP_SEQUENCE);

END
/
CREATE TABLE IDN_OPENID_REMEMBER_ME (
            USER_NAME VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER DEFAULT 0 NOT NULL,
            COOKIE_VALUE VARCHAR(1024),
            CREATED_TIME TIMESTAMP,
            PRIMARY KEY (USER_NAME, TENANT_ID))
/
CREATE TABLE IDN_OPENID_USER_RPS (
			USER_NAME VARCHAR(255) NOT NULL,
			TENANT_ID INTEGER DEFAULT 0 NOT NULL,
			RP_URL VARCHAR(255) NOT NULL,
			TRUSTED_ALWAYS VARCHAR(128) DEFAULT 'FALSE',
			LAST_VISIT DATE NOT NULL,
			VISIT_COUNT INTEGER DEFAULT 0,
			DEFAULT_PROFILE_NAME VARCHAR(255) DEFAULT 'DEFAULT',
			PRIMARY KEY (USER_NAME, TENANT_ID, RP_URL))
/
CREATE TABLE IDN_OPENID_ASSOCIATIONS (
			HANDLE VARCHAR(255) NOT NULL,
			ASSOC_TYPE VARCHAR(255) NOT NULL,
			EXPIRE_IN TIMESTAMP NOT NULL,
			MAC_KEY VARCHAR(255) NOT NULL,
			ASSOC_STORE VARCHAR(128) DEFAULT 'SHARED',
			TENANT_ID INTEGER DEFAULT -1,
			PRIMARY KEY (HANDLE))
/
CREATE TABLE IDN_STS_STORE (
            ID INTEGER NOT NULL,
            TOKEN_ID VARCHAR(255) NOT NULL,
            TOKEN_CONTENT BLOB NOT NULL,
            CREATE_DATE TIMESTAMP NOT NULL,
            EXPIRE_DATE TIMESTAMP NOT NULL,
            STATE INTEGER DEFAULT 0,
            PRIMARY KEY (ID))
/
CREATE SEQUENCE IDN_STS_STORE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_STS_STORE_TRIGGER NO CASCADE BEFORE INSERT ON IDN_STS_STORE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR IDN_STS_STORE_SEQUENCE);

END
/
CREATE TABLE IDN_IDENTITY_USER_DATA (
            TENANT_ID INTEGER DEFAULT -1234 NOT NULL,
            USER_NAME VARCHAR(255) NOT NULL,
            DATA_KEY VARCHAR(255) NOT NULL,
            DATA_VALUE VARCHAR(2048),
            PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY))
/
CREATE TABLE IDN_IDENTITY_META_DATA (
            USER_NAME VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER DEFAULT -1234 NOT NULL,
            METADATA_TYPE VARCHAR(255) NOT NULL,
            METADATA VARCHAR(255) NOT NULL,
            VALID VARCHAR(255) NOT NULL,
            PRIMARY KEY (TENANT_ID, USER_NAME, METADATA_TYPE,METADATA))
/
CREATE TABLE IDN_THRIFT_SESSION (
            SESSION_ID VARCHAR(255) NOT NULL,
            USER_NAME VARCHAR(255) NOT NULL,
            CREATED_TIME VARCHAR(255) NOT NULL,
            LAST_MODIFIED_TIME VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (SESSION_ID)
)
/

CREATE TABLE IDN_AUTH_SESSION_STORE (
            SESSION_ID VARCHAR (100) NOT NULL,
            SESSION_TYPE VARCHAR(100) NOT NULL,
            OPERATION VARCHAR(10) NOT NULL,
            SESSION_OBJECT BLOB,
            TIME_CREATED BIGINT NOT NULL,
            TENANT_ID INTEGER DEFAULT -1,
            EXPIRY_TIME BIGINT NOT NULL,
            PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
)
/

CREATE TABLE IDN_AUTH_TEMP_SESSION_STORE (
            SESSION_ID VARCHAR (100) NOT NULL,
            SESSION_TYPE VARCHAR(100) NOT NULL,
            OPERATION VARCHAR(10) NOT NULL,
            SESSION_OBJECT BLOB,
            TIME_CREATED BIGINT NOT NULL,
            TENANT_ID INTEGER DEFAULT -1,
            EXPIRY_TIME BIGINT NOT NULL,
            PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
)
/

CREATE TABLE IDN_AUTH_USER (
            USER_ID VARCHAR(255) NOT NULL,
            USER_NAME VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            DOMAIN_NAME VARCHAR(255) NOT NULL,
            IDP_ID INTEGER NOT NULL,
            PRIMARY KEY (USER_ID),
            CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID)
)
/

CREATE TABLE IDN_AUTH_USER_SESSION_MAPPING (
            ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            USER_ID VARCHAR(255) NOT NULL,
            SESSION_ID VARCHAR(255) NOT NULL,
            CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID),
            PRIMARY KEY (ID)
)
/

CREATE TABLE IDN_AUTH_SESSION_APP_INFO (
            SESSION_ID VARCHAR (100) NOT NULL,
            SUBJECT VARCHAR (100) NOT NULL,
            APP_ID INTEGER NOT NULL,
            INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
            PRIMARY KEY (SESSION_ID, SUBJECT, APP_ID, INBOUND_AUTH_TYPE)
)
/

CREATE TABLE IDN_AUTH_SESSION_META_DATA (
            SESSION_ID VARCHAR (100) NOT NULL,
            PROPERTY_TYPE VARCHAR (100) NOT NULL,
            VALUE VARCHAR (255) NOT NULL,
            PRIMARY KEY (SESSION_ID, PROPERTY_TYPE, VALUE)
)
/

CREATE TABLE SP_APP (
        ID INTEGER NOT NULL,
        TENANT_ID INTEGER NOT NULL,
        APP_NAME VARCHAR (255) NOT NULL ,
        USER_STORE VARCHAR (255) NOT NULL,
        USERNAME VARCHAR (255) NOT NULL ,
        DESCRIPTION VARCHAR (1024),
        ROLE_CLAIM VARCHAR (512),
        AUTH_TYPE VARCHAR (255) NOT NULL,
        PROVISIONING_USERSTORE_DOMAIN VARCHAR (512),
        IS_LOCAL_CLAIM_DIALECT CHAR(1) DEFAULT '1',
        IS_SEND_LOCAL_SUBJECT_ID CHAR(1) DEFAULT '0',
        IS_SEND_AUTH_LIST_OF_IDPS CHAR(1) DEFAULT '0',
        IS_USE_TENANT_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
        IS_USE_USER_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
        ENABLE_AUTHORIZATION CHAR(1) DEFAULT '0',
        SUBJECT_CLAIM_URI VARCHAR (512),
        IS_SAAS_APP CHAR(1) DEFAULT '0',
        IS_DUMB_MODE CHAR(1) DEFAULT '0',
        UUID CHAR(36) NOT NULL,
        IMAGE_URL VARCHAR(1024),
        ACCESS_URL VARCHAR(1024),
        IS_DISCOVERABLE CHAR(1) DEFAULT '0',

        PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_APP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_APP_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_APP
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_APP_SEQ);
                END
/
ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID)
/
ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_UUID_CONSTRAINT UNIQUE(UUID)
/


CREATE TABLE SP_METADATA (
            ID INTEGER NOT NULL,
            SP_ID INTEGER NOT NULL,
            NAME VARCHAR(255) NOT NULL,
            VALUE VARCHAR(255) NOT NULL,
            DISPLAY_NAME VARCHAR(255),
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (ID),
            CONSTRAINT SP_METADATA_CONSTRAINT UNIQUE (SP_ID, NAME),
            FOREIGN KEY (SP_ID) REFERENCES SP_APP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE SP_METADATA_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_METADATA_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_METADATA
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_METADATA_SEQ);
                END
/

CREATE TABLE SP_INBOUND_AUTH (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            INBOUND_AUTH_KEY VARCHAR (255),
            INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
            INBOUND_CONFIG_TYPE VARCHAR (255) NOT NULL,
            PROP_NAME VARCHAR (255),
            PROP_VALUE VARCHAR (1024) ,
            APP_ID INTEGER NOT NULL,
            PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_INBOUND_AUTH_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_INBOUND_AUTH_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_INBOUND_AUTH
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_INBOUND_AUTH_SEQ);
                END
/
ALTER TABLE SP_INBOUND_AUTH ADD CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_AUTH_STEP (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            STEP_ORDER INTEGER DEFAULT 1,
            APP_ID INTEGER NOT NULL ,
            IS_SUBJECT_STEP CHAR(1) DEFAULT '0',
            IS_ATTRIBUTE_STEP CHAR(1) DEFAULT '0',
            PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_AUTH_STEP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_AUTH_STEP_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_AUTH_STEP
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_AUTH_STEP_SEQ);
                END
/
ALTER TABLE SP_AUTH_STEP ADD CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_FEDERATED_IDP (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            AUTHENTICATOR_ID INTEGER NOT NULL,
            PRIMARY KEY (ID, AUTHENTICATOR_ID))
/
ALTER TABLE SP_FEDERATED_IDP ADD CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE
/

CREATE TABLE SP_CLAIM_DIALECT (
	   	ID INTEGER NOT NULL,
	   	TENANT_ID INTEGER NOT NULL,
	   	SP_DIALECT VARCHAR (512) NOT NULL,
	   	APP_ID INTEGER NOT NULL,
	   	PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_CLAIM_DIALECT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_CLAIM_DIALECT_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_CLAIM_DIALECT
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_CLAIM_DIALECT_SEQ);
                END
/
ALTER TABLE SP_CLAIM_DIALECT ADD CONSTRAINT DIALECTID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_CLAIM_MAPPING (
	    	ID INTEGER NOT NULL,
	    	TENANT_ID INTEGER NOT NULL,
	    	IDP_CLAIM VARCHAR (512) NOT NULL ,
	    	SP_CLAIM VARCHAR (512) NOT NULL ,
	    	APP_ID INTEGER NOT NULL,
	    	IS_REQUESTED VARCHAR(128) DEFAULT '0',
		IS_MANDATORY VARCHAR(128) DEFAULT '0',
	    	DEFAULT_VALUE VARCHAR(255),
	    	PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_CLAIM_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_CLAIM_MAPPING_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_CLAIM_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_CLAIM_MAPPING_SEQ);
                END
/
ALTER TABLE SP_CLAIM_MAPPING ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_ROLE_MAPPING (
	    	ID INTEGER NOT NULL,
	    	TENANT_ID INTEGER NOT NULL,
	    	IDP_ROLE VARCHAR (255) NOT NULL ,
	    	SP_ROLE VARCHAR (255) NOT NULL ,
	    	APP_ID INTEGER NOT NULL,
	    	PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_ROLE_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_ROLE_MAPPING_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_ROLE_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_ROLE_MAPPING_SEQ);
                END
/
ALTER TABLE SP_ROLE_MAPPING ADD CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_REQ_PATH_AUTHENTICATOR (
	    	ID INTEGER NOT NULL,
	    	TENANT_ID INTEGER NOT NULL,
	    	AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
	    	APP_ID INTEGER NOT NULL,
	    	PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_REQ_PATH_AUTH_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_REQ_PATH_AUTH_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_REQ_PATH_AUTHENTICATOR
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_REQ_PATH_AUTH_SEQ);
                END
/
ALTER TABLE SP_REQ_PATH_AUTHENTICATOR ADD CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_PROVISIONING_CONNECTOR (
	    	ID INTEGER NOT NULL,
	    	TENANT_ID INTEGER NOT NULL,
	    	IDP_NAME VARCHAR (255) NOT NULL ,
	    	CONNECTOR_NAME VARCHAR (255) NOT NULL ,
	    	APP_ID INTEGER NOT NULL,
	    	IS_JIT_ENABLED CHAR(1) NOT NULL DEFAULT '0',
	    	BLOCKING CHAR(1) NOT NULL DEFAULT '0',
	    	RULE_ENABLED CHAR(1) NOT NULL DEFAULT '0',
	    	PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_PROV_CONNECTOR_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_PROV_CONNECTOR_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_PROVISIONING_CONNECTOR
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_PROV_CONNECTOR_SEQ);
                END
/
ALTER TABLE SP_PROVISIONING_CONNECTOR ADD CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
/
CREATE TABLE SP_AUTH_SCRIPT (
  ID         INTEGER      NOT NULL,
  TENANT_ID  INTEGER      NOT NULL,
  APP_ID     INTEGER      NOT NULL,
  TYPE       VARCHAR(255) NOT NULL,
  CONTENT    BLOB    DEFAULT NULL,
  IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (ID))
/
CREATE SEQUENCE SP_AUTH_SCRIPT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_AUTH_SCRIPT_TRIG NO CASCADE
            BEFORE INSERT
            ON SP_AUTH_SCRIPT
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_AUTH_SCRIPT_SEQ);
                END
/
CREATE TABLE SP_TEMPLATE (
  ID         INTEGER NOT NULL,
  TENANT_ID  INTEGER NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  DESCRIPTION VARCHAR(1023),
  CONTENT BLOB DEFAULT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME))
/
CREATE SEQUENCE SP_TEMPLATE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
  /
CREATE TRIGGER SP_TEMPLATE_SEQUENCE_TRIGGER NO CASCADE
            BEFORE INSERT
            ON SP_TEMPLATE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR SP_TEMPLATE_SEQUENCE);
                END
/
CREATE TABLE IDN_AUTH_WAIT_STATUS (
  ID              INTEGER                NOT NULL,
  TENANT_ID       INTEGER                NOT NULL,
  LONG_WAIT_KEY   VARCHAR(255)           NOT NULL,
  WAIT_STATUS     CHAR(1) NOT NULL DEFAULT '1',
  TIME_CREATED    TIMESTAMP,
  EXPIRE_TIME     TIMESTAMP,
  PRIMARY KEY (ID),
  CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY))
/
CREATE SEQUENCE IDN_AUTH_WAIT_STATUS_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_AUTH_WAIT_STATUS_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_AUTH_WAIT_STATUS
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_AUTH_WAIT_STATUS_SEQ);
                END
/
CREATE TABLE IDP (
			ID INTEGER NOT NULL,
			TENANT_ID INTEGER NOT NULL,
			NAME VARCHAR(254) NOT NULL,
			IS_ENABLED CHAR(1) NOT NULL DEFAULT '1',
			IS_PRIMARY CHAR(1) NOT NULL DEFAULT '0',
			HOME_REALM_ID VARCHAR(254),
			IMAGE BLOB,
			CERTIFICATE BLOB,
			ALIAS VARCHAR(254),
			INBOUND_PROV_ENABLED CHAR (1) NOT NULL DEFAULT '0',
			INBOUND_PROV_USER_STORE_ID VARCHAR(254),
 			USER_CLAIM_URI VARCHAR(254),
 			ROLE_CLAIM_URI VARCHAR(254),
 			DESCRIPTION VARCHAR (1024),
 			DEFAULT_AUTHENTICATOR_NAME VARCHAR(254),
 			DEFAULT_PRO_CONNECTOR_NAME VARCHAR(254),
 			PROVISIONING_ROLE VARCHAR(128),
 			IS_FEDERATION_HUB CHAR(1) NOT NULL DEFAULT '0',
 			IS_LOCAL_CLAIM_DIALECT CHAR(1) NOT NULL DEFAULT '0',
 			DISPLAY_NAME VARCHAR(254),
 			IMAGE_URL VARCHAR(1024),
 			UUID CHAR(36) NOT NULL,
 			PRIMARY KEY (ID),
 			UNIQUE (TENANT_ID, NAME),
 			UNIQUE (UUID))
/
CREATE SEQUENCE IDP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_SEQ);
                END
/
CREATE TABLE IDP_ROLE (
			ID INTEGER NOT NULL,
			IDP_ID INTEGER NOT NULL,
			TENANT_ID INTEGER NOT NULL,
			ROLE VARCHAR(254) NOT NULL,
			PRIMARY KEY (ID),
			UNIQUE (IDP_ID, ROLE),
			FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_ROLE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_ROLE_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_ROLE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_ROLE_SEQ);
                END
/
CREATE TABLE IDP_ROLE_MAPPING (
			ID INTEGER NOT NULL,
			IDP_ROLE_ID INTEGER NOT NULL,
			TENANT_ID INTEGER NOT NULL,
			USER_STORE_ID VARCHAR (253) NOT NULL,
			LOCAL_ROLE VARCHAR(253) NOT NULL,
			PRIMARY KEY (ID),
			UNIQUE (IDP_ROLE_ID, TENANT_ID, USER_STORE_ID, LOCAL_ROLE),
			FOREIGN KEY (IDP_ROLE_ID) REFERENCES IDP_ROLE(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_ROLE_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_ROLE_MAPPING_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_ROLE_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_ROLE_MAPPING_SEQ);
                END
/
CREATE TABLE IDP_CLAIM (
			ID INTEGER NOT NULL,
			IDP_ID INTEGER NOT NULL,
			TENANT_ID INTEGER NOT NULL,
			CLAIM VARCHAR(254) NOT NULL,
			PRIMARY KEY (ID),
			UNIQUE (IDP_ID, CLAIM),
			FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_CLAIM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_CLAIM_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_CLAIM
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_CLAIM_SEQ);
                END
/
CREATE TABLE IDP_CLAIM_MAPPING (
			ID INTEGER NOT NULL,
			IDP_CLAIM_ID INTEGER NOT NULL,
			TENANT_ID INTEGER NOT NULL,
			LOCAL_CLAIM VARCHAR(253) NOT NULL,
			DEFAULT_VALUE VARCHAR(255),
			IS_REQUESTED VARCHAR(128) DEFAULT '0',
			PRIMARY KEY (ID),
			UNIQUE (IDP_CLAIM_ID, TENANT_ID, LOCAL_CLAIM),
			FOREIGN KEY (IDP_CLAIM_ID) REFERENCES IDP_CLAIM(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_CLAIM_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_CLAIM_MAPPING_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_CLAIM_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_CLAIM_MAPPING_SEQ);
                END
/
CREATE TABLE IDP_AUTHENTICATOR (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            IDP_ID INTEGER NOT NULL,
            NAME VARCHAR(255) NOT NULL,
            IS_ENABLED CHAR (1) DEFAULT '1',
            DISPLAY_NAME VARCHAR(255),
            PRIMARY KEY (ID),
            UNIQUE (TENANT_ID, IDP_ID, NAME),
            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_AUTHENTICATOR_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_AUTHENTICATOR_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_AUTHENTICATOR
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_AUTHENTICATOR_SEQ);
                END
/

CREATE TABLE IDP_METADATA (
            ID INTEGER NOT NULL,
            IDP_ID INTEGER NOT NULL,
            NAME VARCHAR(255) NOT NULL,
            VALUE VARCHAR(255),
            DISPLAY_NAME VARCHAR(255),
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (ID),
            CONSTRAINT IDP_METADATA_CONSTRAINT UNIQUE (IDP_ID, NAME),
            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/

CREATE SEQUENCE IDP_METADATA_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_METADATA_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_METADATA
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_METADATA_SEQ);
                END
/
CREATE TABLE IDP_AUTHENTICATOR_PROPERTY (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            AUTHENTICATOR_ID INTEGER NOT NULL,
            PROPERTY_KEY VARCHAR(255) NOT NULL,
            PROPERTY_VALUE VARCHAR(2047),
            IS_SECRET CHAR (1) DEFAULT '0',
            PRIMARY KEY (ID),
            UNIQUE (TENANT_ID, AUTHENTICATOR_ID, PROPERTY_KEY),
            FOREIGN KEY (AUTHENTICATOR_ID) REFERENCES IDP_AUTHENTICATOR(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_AUTHENTICATOR_PROP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_AUTHENTICATOR_PROP_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_AUTHENTICATOR_PROPERTY
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_AUTHENTICATOR_PROP_SEQ);
                END
/

CREATE TABLE IDP_PROVISIONING_CONFIG (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            IDP_ID INTEGER NOT NULL,
            PROVISIONING_CONNECTOR_TYPE VARCHAR(255) NOT NULL,
            IS_ENABLED CHAR (1) DEFAULT '0',
            IS_BLOCKING CHAR (1) DEFAULT '0',
            IS_RULES_ENABLED CHAR (1) DEFAULT '0',
            PRIMARY KEY (ID),
            UNIQUE (TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE),
            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_PROV_CONFIG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_PROV_CONFIG_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_PROVISIONING_CONFIG
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_PROV_CONFIG_SEQ);
                END
/
CREATE TABLE IDP_PROV_CONFIG_PROPERTY (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            PROVISIONING_CONFIG_ID INTEGER NOT NULL,
            PROPERTY_KEY VARCHAR(255) NOT NULL,
            PROPERTY_VALUE VARCHAR(2048),
            PROPERTY_BLOB_VALUE BLOB,
            PROPERTY_TYPE CHAR(32) NOT NULL,
            IS_SECRET CHAR (1) DEFAULT '0',
            PRIMARY KEY (ID),
            UNIQUE (TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY),
            FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_PROV_CONFIG_PROP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_PROV_CONFIG_PROP_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_PROV_CONFIG_PROPERTY
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_PROV_CONFIG_PROP_SEQ);
                END
/
CREATE TABLE IDP_PROVISIONING_ENTITY (
            ID INTEGER NOT NULL,
            PROVISIONING_CONFIG_ID INTEGER NOT NULL,
            ENTITY_TYPE VARCHAR(255) NOT NULL,
            ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL,
            ENTITY_NAME VARCHAR(255) NOT NULL,
            ENTITY_VALUE VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            ENTITY_LOCAL_ID VARCHAR(255),
            PRIMARY KEY (ID),
            UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME, PROVISIONING_CONFIG_ID),
            UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, ENTITY_VALUE),
            FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_PROV_ENTITY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_PROV_ENTITY_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_PROVISIONING_ENTITY
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_PROV_ENTITY_SEQ);
                END
/
CREATE TABLE IDP_LOCAL_CLAIM (
            ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            IDP_ID INTEGER NOT NULL,
            CLAIM_URI VARCHAR(255) NOT NULL,
            DEFAULT_VALUE VARCHAR(255),
       	    IS_REQUESTED VARCHAR(128) DEFAULT '0',
            PRIMARY KEY (ID),
            UNIQUE (TENANT_ID, IDP_ID, CLAIM_URI),
            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDP_LOCAL_CLAIM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDP_LOCAL_CLAIM_TRIG NO CASCADE
            BEFORE INSERT
            ON IDP_LOCAL_CLAIM
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDP_LOCAL_CLAIM_SEQ);
                END
/
CREATE TABLE IDN_ASSOCIATED_ID (
            ID INTEGER NOT NULL,
            IDP_USER_ID VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL DEFAULT -1234,
            IDP_ID INTEGER NOT NULL,
            DOMAIN_NAME VARCHAR(255) NOT NULL,
            USER_NAME VARCHAR(255) NOT NULL,
            ASSOCIATION_ID CHAR(36) NOT NULL,
            PRIMARY KEY (ID),
            UNIQUE(IDP_USER_ID, TENANT_ID, IDP_ID),
            FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE)
/

CREATE SEQUENCE IDN_ASSOCIATED_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_ASSOCIATED_ID_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_ASSOCIATED_ID
	    REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_ASSOCIATED_ID_SEQ);
                END
/
CREATE TABLE IDN_USER_ACCOUNT_ASSOCIATION (
            ASSOCIATION_KEY VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            DOMAIN_NAME VARCHAR(255) NOT NULL,
            USER_NAME VARCHAR(255) NOT NULL,
            PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME))
/
CREATE TABLE FIDO_DEVICE_STORE (
        TENANT_ID INTEGER NOT NULL,
        DOMAIN_NAME VARCHAR(255) NOT NULL,
        USER_NAME VARCHAR(45) NOT NULL,
        TIME_REGISTERED TIMESTAMP,
        KEY_HANDLE VARCHAR(200) NOT NULL,
        DEVICE_DATA VARCHAR(2048) NOT NULL,
        PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME, KEY_HANDLE))
/

CREATE TABLE FIDO2_DEVICE_STORE (
        TENANT_ID INTEGER NOT NULL,
        DOMAIN_NAME VARCHAR(255) NOT NULL,
        USER_NAME VARCHAR(45) NOT NULL,
        TIME_REGISTERED TIMESTAMP,
        USER_HANDLE VARCHAR(64) NOT NULL,
        CREDENTIAL_ID VARCHAR(200) NOT NULL,
        PUBLIC_KEY_COSE VARCHAR(1024) NOT NULL,
        SIGNATURE_COUNT BIGINT,
        USER_IDENTITY VARCHAR(512) NOT NULL,
        DISPLAY_NAME VARCHAR(255),
        IS_USERNAMELESS_SUPPORTED CHAR(1) DEFAULT '0',
        PRIMARY KEY (CREDENTIAL_ID, USER_HANDLE))
/

CREATE TABLE WF_REQUEST (
    UUID VARCHAR (45) NOT NULL,
    CREATED_BY VARCHAR (255),
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    OPERATION_TYPE VARCHAR (50),
    CREATED_AT TIMESTAMP,
    UPDATED_AT TIMESTAMP,
    STATUS VARCHAR (30),
    REQUEST BLOB,
    PRIMARY KEY (UUID))
/

CREATE TABLE WF_BPS_PROFILE (
    PROFILE_NAME VARCHAR(45) NOT NULL,
    HOST_URL_MANAGER VARCHAR(255),
    HOST_URL_WORKER VARCHAR(255),
    USERNAME VARCHAR(100),
    PASSWORD VARCHAR(1023),
    CALLBACK_HOST VARCHAR (45),
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    PRIMARY KEY (PROFILE_NAME, TENANT_ID))
/

CREATE TABLE WF_WORKFLOW(
    ID VARCHAR (45) NOT NULL,
    WF_NAME VARCHAR (45),
    DESCRIPTION VARCHAR (255),
    TEMPLATE_ID VARCHAR (45),
    IMPL_ID VARCHAR (45),
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    PRIMARY KEY (ID))
/

CREATE TABLE WF_WORKFLOW_ASSOCIATION(
    ID INTEGER NOT NULL,
    ASSOC_NAME VARCHAR (45),
    EVENT_ID VARCHAR(45),
    ASSOC_CONDITION VARCHAR (2000),
    WORKFLOW_ID VARCHAR (45),
    IS_ENABLED CHAR (1) DEFAULT '1',
    TENANT_ID INTEGER DEFAULT -1,
    PRIMARY KEY(ID),
    FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE)
/

CREATE SEQUENCE WF_WORKFLOW_ASSOCIATION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER WF_WORKFLOW_ASSOCIATION_TRIG NO CASCADE
    BEFORE INSERT
    ON WF_WORKFLOW_ASSOCIATION
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
        SET (NEW.ID) = (NEXTVAL FOR WF_WORKFLOW_ASSOCIATION_SEQ);
      END
/

CREATE TABLE WF_WORKFLOW_CONFIG_PARAM(
    WORKFLOW_ID VARCHAR (45) NOT NULL,
    PARAM_NAME VARCHAR (45) NOT NULL,
    PARAM_VALUE VARCHAR (1000),
    PARAM_QNAME VARCHAR (45) NOT NULL,
    PARAM_HOLDER VARCHAR (45) NOT NULL,
    TENANT_ID INTEGER DEFAULT -1,
    PRIMARY KEY (WORKFLOW_ID, PARAM_NAME, PARAM_QNAME, PARAM_HOLDER),
    FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE)
/

CREATE TABLE WF_REQUEST_ENTITY_RELATIONSHIP(
  REQUEST_ID VARCHAR (45) NOT NULL,
  ENTITY_NAME VARCHAR (255) NOT NULL,
  ENTITY_TYPE VARCHAR (50) NOT NULL,
  TENANT_ID INTEGER NOT NULL DEFAULT -1,
  PRIMARY KEY(REQUEST_ID, ENTITY_NAME, ENTITY_TYPE, TENANT_ID),
  FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE)
/

CREATE TABLE WF_WORKFLOW_REQUEST_RELATION(
  RELATIONSHIP_ID VARCHAR (45) NOT NULL,
  WORKFLOW_ID VARCHAR (45),
  REQUEST_ID VARCHAR (45),
  UPDATED_AT TIMESTAMP,
  STATUS VARCHAR (30),
  TENANT_ID INTEGER DEFAULT -1,
  PRIMARY KEY (RELATIONSHIP_ID),
  FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE,
  FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE)
/

CREATE TABLE IDN_RECOVERY_DATA (
  USER_NAME VARCHAR(255) NOT NULL,
  USER_DOMAIN VARCHAR(127) NOT NULL,
  TENANT_ID INTEGER DEFAULT -1 NOT NULL,
  CODE VARCHAR(255) NOT NULL,
  SCENARIO VARCHAR(255) NOT NULL,
  STEP VARCHAR(127) NOT NULL,
  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  REMAINING_SETS VARCHAR(2500),
  PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
  UNIQUE(CODE))
/

CREATE TABLE IDN_PASSWORD_HISTORY_DATA (
  ID INTEGER NOT NULL,
  USER_NAME   VARCHAR(255) NOT NULL,
  USER_DOMAIN VARCHAR(127) NOT NULL,
  TENANT_ID   INTEGER DEFAULT -1 NOT NULL,
  SALT_VALUE  VARCHAR(255) NOT NULL,
  HASH        VARCHAR(255) NOT NULL,
  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ID),
  UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH)
  )
/

CREATE SEQUENCE IDN_PASSWORD_HISTORY_DATA_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_PASSWORD_HISTORY_DATA NO CASCADE
    BEFORE INSERT
    ON IDN_PASSWORD_HISTORY_DATA
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
        SET (NEW.ID) = (NEXTVAL FOR IDN_PASSWORD_HISTORY_DATA_SEQ);
      END
/

CREATE TABLE IDN_CLAIM_DIALECT (
  ID INTEGER NOT NULL,
  DIALECT_URI VARCHAR (255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID))
/
CREATE SEQUENCE IDN_CLAIM_DIALECT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CLAIM_DIALECT_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_CLAIM_DIALECT
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_CLAIM_DIALECT_SEQ);
                END
/

CREATE TABLE IDN_CLAIM (
  ID INTEGER NOT NULL,
  DIALECT_ID INTEGER NOT NULL,
  CLAIM_URI VARCHAR (255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
  CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID))
/
CREATE SEQUENCE IDN_CLAIM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CLAIM_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_CLAIM
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_CLAIM_SEQ);
                END
/

CREATE TABLE IDN_CLAIM_MAPPED_ATTRIBUTE (
  ID INTEGER NOT NULL,
  LOCAL_CLAIM_ID INTEGER NOT NULL,
  USER_STORE_DOMAIN_NAME VARCHAR (255) NOT NULL,
  ATTRIBUTE_NAME VARCHAR (255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
  CONSTRAINT USER_STORE_DOMAIN_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, USER_STORE_DOMAIN_NAME, TENANT_ID))
/
CREATE SEQUENCE IDN_CLAIM_MAPPED_ATTRIBUTE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CLAIM_MAPPED_ATTR_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_CLAIM_MAPPED_ATTRIBUTE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_CLAIM_MAPPED_ATTRIBUTE_SEQ);
                END
/

CREATE TABLE IDN_CLAIM_PROPERTY (
  ID INTEGER NOT NULL,
  LOCAL_CLAIM_ID INTEGER NOT NULL,
  PROPERTY_NAME VARCHAR (255) NOT NULL,
  PROPERTY_VALUE VARCHAR (255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
  CONSTRAINT PROPERTY_NAME_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, PROPERTY_NAME, TENANT_ID))
/
CREATE SEQUENCE IDN_CLAIM_PROPERTY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CLAIM_PROPERTY_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_CLAIM_PROPERTY
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_CLAIM_PROPERTY_SEQ);
                END
/

CREATE TABLE IDN_CLAIM_MAPPING (
  ID INTEGER NOT NULL,
  EXT_CLAIM_ID INTEGER NOT NULL,
  MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
  FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
  CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID))
/
CREATE SEQUENCE IDN_CLAIM_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CLAIM_MAPPING_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_CLAIM_MAPPING
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_CLAIM_MAPPING_SEQ);
                END
/

CREATE TABLE IDN_SAML2_ASSERTION_STORE (
  ID INTEGER NOT NULL,
  SAML2_ID  VARCHAR(255) ,
  SAML2_ISSUER  VARCHAR(255) ,
  SAML2_SUBJECT  VARCHAR(255) ,
  SAML2_SESSION_INDEX  VARCHAR(255) ,
  SAML2_AUTHN_CONTEXT_CLASS_REF  VARCHAR(255) ,
  SAML2_ASSERTION  VARCHAR(4096) ,
  ASSERTION BLOB ,
  PRIMARY KEY (ID))
/
CREATE SEQUENCE IDN_SAML2_ASSERTION_STORE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_SAML2_ASSERTION_STORE_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_SAML2_ASSERTION_STORE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_SAML2_ASSERTION_STORE_SEQ);
                END
/

CREATE TABLE IDN_SAML2_ARTIFACT_STORE (
  ID INTEGER NOT NULL,
  SOURCE_ID VARCHAR(255) NOT NULL,
  MESSAGE_HANDLER VARCHAR(255) NOT NULL,
  AUTHN_REQ_DTO BLOB NOT NULL,
  SESSION_ID VARCHAR(255) NOT NULL,
  INIT_TIMESTAMP TIMESTAMP NOT NULL,
  EXP_TIMESTAMP TIMESTAMP NOT NULL,
  ASSERTION_ID VARCHAR(255),
  PRIMARY KEY (ID))
/
CREATE SEQUENCE IDN_SAML2_ARTIFACT_STORE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_SAML2_ARTIFACT_STORE_TRIG NO CASCADE
            BEFORE INSERT
              ON IDN_SAML2_ARTIFACT_STORE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_SAML2_ARTIFACT_STORE_SEQ);
                END
/

CREATE TABLE IDN_OIDC_JTI (
  JWT_ID VARCHAR(255) NOT NULL,
  EXP_TIME TIMESTAMP NOT NULL,
  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (JWT_ID))
/


CREATE TABLE IDN_OIDC_PROPERTY (
  ID INTEGER NOT NULL,
  TENANT_ID  INTEGER,
  CONSUMER_KEY  VARCHAR(255) ,
  PROPERTY_KEY  VARCHAR(255) NOT NULL,
  PROPERTY_VALUE  VARCHAR(2047) ,
  PRIMARY KEY (ID),
  FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE)
/
CREATE SEQUENCE IDN_OIDC_PROPERTY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OIDC_PROPERTY_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_OIDC_PROPERTY
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
                BEGIN ATOMIC
                    SET (NEW.ID) = (NEXTVAL FOR IDN_OIDC_PROPERTY_SEQ);
                END
/

CREATE TABLE IDN_OIDC_REQ_OBJECT_REFERENCE (
  ID INTEGER NOT NULL,
  CONSUMER_KEY_ID INTEGER ,
  CODE_ID VARCHAR(255) ,
  TOKEN_ID VARCHAR(255) ,
  SESSION_DATA_KEY VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
  FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE,
  FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDN_OIDC_REQUEST_OBJECT_REF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OIDC_REQUEST_OBJECT_REF_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_OIDC_REQ_OBJECT_REFERENCE
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
                   SET (NEW.ID) = (NEXTVAL FOR IDN_OIDC_REQUEST_OBJECT_REF_SEQ);
               END
/

CREATE TABLE IDN_OIDC_REQ_OBJECT_CLAIMS (
  ID INTEGER NOT NULL,
  REQ_OBJECT_ID INTEGER ,
  CLAIM_ATTRIBUTE VARCHAR(255),
  ESSENTIAL CHAR(1) NOT NULL DEFAULT '0',
  VALUE VARCHAR(255),
  IS_USERINFO CHAR(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (ID),
  FOREIGN KEY (REQ_OBJECT_ID) REFERENCES IDN_OIDC_REQ_OBJECT_REFERENCE(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDN_OIDC_REQ_OBJECT_CLAIMS_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OIDC_REQ_OBJECT_CLAIMS_TRIG NO CASCADE
            BEFORE INSERT
            ON IDN_OIDC_REQ_OBJECT_CLAIMS
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
                   SET (NEW.ID) = (NEXTVAL FOR IDN_OIDC_REQ_OBJECT_CLAIMS_SEQ);
               END
/

CREATE TABLE IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
  ID INTEGER NOT NULL,
  REQ_OBJECT_CLAIMS_ID INTEGER,
  CLAIM_VALUES VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (REQ_OBJECT_CLAIMS_ID) REFERENCES IDN_OIDC_REQ_OBJECT_CLAIMS(ID) ON DELETE CASCADE)
/
CREATE SEQUENCE IDN_OIDC_REQ_OBJECT_CLAIM_VALUES_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OIDC_REQ_OBJECT_CLAIM_VALUES_TRIG
            BEFORE INSERT
            ON IDN_OIDC_REQ_OBJ_CLAIM_VALUES
            REFERENCING NEW AS NEW
            FOR EACH ROW MODE DB2SQL
               BEGIN ATOMIC
                   SET (NEW.ID) = (NEXTVAL FOR IDN_OIDC_REQ_OBJECT_CLAIM_VALUES_SEQ);
               END
/

CREATE TABLE IDN_CERTIFICATE (
            ID INTEGER NOT NULL,
            NAME VARCHAR(100) NOT NULL,
            CERTIFICATE_IN_PEM BLOB,
            TENANT_ID INTEGER NOT NULL,
            CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID),
            PRIMARY KEY (ID))
/
CREATE SEQUENCE IDN_CERTIFICATE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CERTIFICATE_TRIGGER NO CASCADE BEFORE INSERT ON IDN_CERTIFICATE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID)
    = (NEXTVAL FOR IDN_CERTIFICATE_SEQUENCE);
  END
/

CREATE TABLE IDN_OIDC_SCOPE_CLAIM_MAPPING (
            ID INTEGER NOT NULL,
            SCOPE_ID INTEGER NOT NULL,
            EXTERNAL_CLAIM_ID INTEGER NOT NULL,
            PRIMARY KEY (ID),
            FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
            FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
            CONSTRAINT IDX_AT_SI_ECI UNIQUE (SCOPE_ID, EXTERNAL_CLAIM_ID))
/
CREATE SEQUENCE IDN_OIDC_SCOPE_CLAIM_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OIDC_SCOPE_CLAIM_MAP_TRIG NO CASCADE BEFORE INSERT ON IDN_OIDC_SCOPE_CLAIM_MAPPING
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID)
    = (NEXTVAL FOR IDN_OIDC_SCOPE_CLAIM_MAP_SEQ);
  END
/

CREATE TABLE IDN_FUNCTION_LIBRARY (
	NAME VARCHAR(255) NOT NULL,
	DESCRIPTION VARCHAR(1023),
	TYPE VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	DATA BLOB NOT NULL,
	PRIMARY KEY (TENANT_ID,NAME)
)
/

CREATE TABLE IDN_OAUTH2_CIBA_AUTH_CODE (
    AUTH_CODE_KEY CHAR (36) NOT NULL,
    AUTH_REQ_ID CHAR (36) NOT NULL,
    ISSUED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSUMER_KEY VARCHAR(255),
    LAST_POLLED_TIME TIMESTAMP,
    POLLING_INTERVAL INTEGER,
    EXPIRES_IN  INTEGER,
    AUTHENTICATED_USER_NAME VARCHAR(255),
    USER_STORE_DOMAIN VARCHAR(100),
    TENANT_ID INTEGER,
    AUTH_REQ_STATUS VARCHAR(100) DEFAULT 'REQUESTED',
    IDP_ID INTEGER,
    CONSTRAINT AUTH_REQ_ID_CONSTRAINT UNIQUE(AUTH_REQ_ID),
    PRIMARY KEY (AUTH_CODE_KEY),
    FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE)
/

CREATE TABLE IDN_OAUTH2_CIBA_REQUEST_SCOPES (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    AUTH_CODE_KEY CHAR (36) NOT NULL,
    SCOPE VARCHAR (255),
    FOREIGN KEY (AUTH_CODE_KEY) REFERENCES IDN_OAUTH2_CIBA_AUTH_CODE(AUTH_CODE_KEY) ON DELETE CASCADE,
    PRIMARY KEY (ID))
/


CREATE TABLE IDN_FED_AUTH_SESSION_MAPPING (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
	IDP_SESSION_ID VARCHAR(255) NOT NULL,
	SESSION_ID VARCHAR(255) NOT NULL,
	IDP_NAME VARCHAR(255) NOT NULL,
	AUTHENTICATOR_ID VARCHAR(255),
	PROTOCOL_TYPE VARCHAR(255),
	TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	TENANT_ID INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (ID),
	UNIQUE (IDP_SESSION_ID, TENANT_ID)
)
/

CREATE TABLE IDN_CONFIG_TYPE (
    ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    CONSTRAINT TYPE_NAME_CONSTRAINT UNIQUE (NAME)
)
/
INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('9ab0ef95-13e9-4ed5-afaf-d29bed62f7bd', 'IDP_TEMPLATE', 'Template type to uniquely identify IDP templates'),
('3c4ac3d0-5903-4e3d-aaca-38df65b33bfd', 'APPLICATION_TEMPLATE', 'Template type to uniquely identify Application templates'),
('8ec6dbf1-218a-49bf-bc34-0d2db52d151c', 'CORS_CONFIGURATION', 'A resource type to keep the tenant CORS configurations'),
('669b99ca-cdb0-44a6-8cae-babed3b585df', 'Publisher', 'A resource type to keep the event publisher configurations'),
('73f6d9ca-62f4-4566-bab9-2a930ae51ba8', 'BRANDING_PREFERENCES', 'A resource type to keep the tenant branding preferences'),
('899c69b2-8bf7-46b5-9666-f7f99f90d6cc', 'fido-config', 'A resource type to store FIDO authenticator related preferences')
/

CREATE TABLE IDN_CONFIG_RESOURCE (
    ID VARCHAR(255) NOT NULL,
    TENANT_ID INT NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    CREATED_TIME TIMESTAMP NOT NULL,
    LAST_MODIFIED TIMESTAMP NOT NULL,
    HAS_FILE SMALLINT DEFAULT 0 NOT NULL,
    HAS_ATTRIBUTE SMALLINT DEFAULT 0 NOT NULL,
    TYPE_ID VARCHAR(255) NOT NULL,
    UNIQUE (NAME, TENANT_ID, TYPE_ID),
    PRIMARY KEY (ID)
)
/
ALTER TABLE IDN_CONFIG_RESOURCE ADD CONSTRAINT TYPE_ID_FOREIGN_CONSTRAINT FOREIGN KEY (TYPE_ID) REFERENCES
IDN_CONFIG_TYPE (ID) ON DELETE CASCADE ON UPDATE RESTRICT
/

CREATE TABLE IDN_CONFIG_ATTRIBUTE (
    ID VARCHAR(255) NOT NULL,
    RESOURCE_ID VARCHAR(255) NOT NULL,
    ATTR_KEY VARCHAR(255) NOT NULL,
    ATTR_VALUE VARCHAR(1023) NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE (RESOURCE_ID, ATTR_KEY)
)
/
ALTER TABLE IDN_CONFIG_ATTRIBUTE ADD CONSTRAINT RESOURCE_ID_ATTRIBUTE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID)
REFERENCES IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE RESTRICT
/

CREATE TABLE IDN_CONFIG_FILE (
    ID VARCHAR(255) NOT NULL,
    VALUE BLOB NULL,
    NAME VARCHAR(255) NULL,
    RESOURCE_ID VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID)
)
/
ALTER TABLE IDN_CONFIG_FILE ADD CONSTRAINT RESOURCE_ID_FILE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID) REFERENCES
IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE RESTRICT
/

CREATE TABLE IDN_REMOTE_FETCH_CONFIG (
	ID VARCHAR(255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
	IS_ENABLED CHAR(1) NOT NULL,
    REPO_MANAGER_TYPE VARCHAR(255) NOT NULL,
    ACTION_LISTENER_TYPE VARCHAR(255) NOT NULL,
    CONFIG_DEPLOYER_TYPE VARCHAR(255) NOT NULL,
    REMOTE_FETCH_NAME VARCHAR(255),
    REMOTE_RESOURCE_URI VARCHAR(255) NOT NULL,
    ATTRIBUTES_JSON CLOB NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT UC_REMOTE_RESOURCE_TYPE UNIQUE (TENANT_ID, CONFIG_DEPLOYER_TYPE)
    )
/

CREATE TABLE IDN_REMOTE_FETCH_REVISIONS (
	ID VARCHAR(255) NOT NULL,
	CONFIG_ID VARCHAR(255) NOT NULL,
	FILE_PATH VARCHAR(255) NOT NULL,
	FILE_HASH VARCHAR(255),
	DEPLOYED_DATE TIMESTAMP,
	LAST_SYNC_TIME TIMESTAMP,
	DEPLOYMENT_STATUS VARCHAR(255),
	ITEM_NAME VARCHAR(255) NOT NULL,
	DEPLOY_ERR_LOG CLOB,
	PRIMARY KEY (ID),
	FOREIGN KEY (CONFIG_ID) REFERENCES IDN_REMOTE_FETCH_CONFIG(ID) ON DELETE CASCADE,
	CONSTRAINT UC_REVISIONS UNIQUE (CONFIG_ID, ITEM_NAME)
)
/

CREATE TABLE IDN_USER_FUNCTIONALITY_MAPPING (
	ID VARCHAR(255) NOT NULL,
	USER_ID VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
	IS_FUNCTIONALITY_LOCKED BOOLEAN NOT NULL,
	FUNCTIONALITY_UNLOCK_TIME BIGINT NOT NULL,
	FUNCTIONALITY_LOCK_REASON VARCHAR(1023),
	FUNCTIONALITY_LOCK_REASON_CODE VARCHAR(255),
	PRIMARY KEY (ID),
	CONSTRAINT IDN_USER_FUNCTIONALITY_MAPPING_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID)
)
/

CREATE TABLE IDN_USER_FUNCTIONALITY_PROPERTY (
	ID VARCHAR(255) NOT NULL,
	USER_ID VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
	PROPERTY_NAME VARCHAR(255) NOT NULL,
	PROPERTY_VALUE VARCHAR(255),
	PRIMARY KEY (ID),
	CONSTRAINT IDN_USER_FUNCTIONALITY_PROPERTY_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID, PROPERTY_NAME)
)
/

CREATE TABLE IF NOT EXISTS IDN_CORS_ORIGIN (
    ID                INTEGER       NOT NULL,
    TENANT_ID         INTEGER       NOT NULL,
    ORIGIN            VARCHAR(2048) NOT NULL,
    UUID              CHAR(36)      NOT NULL,

    PRIMARY KEY (ID),
    UNIQUE (TENANT_ID, ORIGIN),
    UNIQUE (UUID)
) INDEX IN TS32K
/
CREATE SEQUENCE IDN_CORS_ORIGIN_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_CORS_ORIGIN_TRIG NO CASCADE
    BEFORE INSERT
    ON IDN_CORS_ORIGIN
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.ID) = (NEXTVAL FOR IDN_CORS_ORIGIN_SEQ);
        END
/

CREATE TABLE IDN_CORS_ASSOCIATION (
    IDN_CORS_ORIGIN_ID INT NOT NULL,
    SP_APP_ID          INT NOT NULL,

    PRIMARY KEY (IDN_CORS_ORIGIN_ID, SP_APP_ID),
    FOREIGN KEY (IDN_CORS_ORIGIN_ID) REFERENCES IDN_CORS_ORIGIN (ID) ON DELETE CASCADE,
    FOREIGN KEY (SP_APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
)
/

CREATE TABLE IDN_OAUTH2_USER_CONSENT (
    ID INTEGER NOT NULL,
    USER_ID VARCHAR(255) NOT NULL,
    APP_ID CHAR(36) NOT NULL,
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    CONSENT_ID VARCHAR(255) NOT NULL,

    PRIMARY KEY (ID),
    FOREIGN KEY (APP_ID) REFERENCES SP_APP (UUID) ON DELETE CASCADE,
    UNIQUE (USER_ID, APP_ID, TENANT_ID),
    UNIQUE (CONSENT_ID)
)
/
CREATE SEQUENCE IDN_OAUTH2_USER_CONSENT_PK_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OAUTH2_USER_CONSENT_TRIG NO CASCADE
    BEFORE INSERT
    ON IDN_OAUTH2_USER_CONSENT
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.ID) = (NEXTVAL FOR IDN_OAUTH2_USER_CONSENT_PK_SEQ);
        END
/

CREATE TABLE IDN_OAUTH2_USER_CONSENTED_SCOPES (
    ID INTEGER NOT NULL,
    CONSENT_ID VARCHAR(255) NOT NULL,
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    SCOPE VARCHAR(255) NOT NULL,
    CONSENT BOOLEAN WITH DEFAULT TRUE,

    PRIMARY KEY (ID),
    FOREIGN KEY (CONSENT_ID) REFERENCES IDN_OAUTH2_USER_CONSENT (CONSENT_ID) ON DELETE CASCADE,
    UNIQUE (CONSENT_ID, SCOPE)
)
/
CREATE SEQUENCE IDN_OAUTH2_USER_CONSENTED_SCOPES_PK_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER IDN_OAUTH2_USER_CONSENTED_SCOPES_TRIG NO CASCADE
    BEFORE INSERT
    ON IDN_OAUTH2_USER_CONSENTED_SCOPES
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.ID) = (NEXTVAL FOR IDN_OAUTH2_USER_CONSENTED_SCOPES_PK_SEQ);
        END
/

CREATE TABLE IDN_SECRET_TYPE (
    ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    CONSTRAINT SECRET_TYPE_NAME_CONSTRAINT UNIQUE (NAME)
)
/

INSERT INTO IDN_SECRET_TYPE (ID, NAME, DESCRIPTION) VALUES
('1358bdbf-e0cc-4268-a42c-c3e0960e13f0', 'ADAPTIVE_AUTH_CALL_CHOREO', 'Secret type to uniquely identify secrets relevant to callChoreo adaptive auth function')
/

CREATE TABLE IDN_SECRET (
    ID VARCHAR(255) NOT NULL,
    TENANT_ID INT NOT NULL,
    SECRET_NAME VARCHAR(255) NOT NULL,
    SECRET_VALUE VARCHAR(8000) NOT NULL,
    CREATED_TIME TIMESTAMP NOT NULL,
    LAST_MODIFIED TIMESTAMP NOT NULL,
    TYPE_ID       VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (TYPE_ID) REFERENCES IDN_SECRET_TYPE(ID) ON DELETE CASCADE,
    UNIQUE (SECRET_NAME, TENANT_ID, TYPE_ID)
)
/

CREATE TABLE IF NOT EXISTS SP_SHARED_APP (
	ID INTEGER NOT NULL,
	MAIN_APP_ID CHAR(36) NOT NULL,
	OWNER_ORG_ID CHAR(36) NOT NULL,
	SHARED_APP_ID CHAR(36) NOT NULL,
	SHARED_ORG_ID CHAR(36) NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (MAIN_APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE,
	FOREIGN KEY (SHARED_APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE,
	UNIQUE (MAIN_APP_ID, OWNER_ORG_ID, SHARED_ORG_ID),
	UNIQUE (SHARED_APP_ID)
)
/
CREATE SEQUENCE SP_SHARED_APP_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER SP_SHARED_APP_TRIG NO CASCADE
    BEFORE INSERT
    ON SP_SHARED_APP
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.ID) = (NEXTVAL FOR SP_SHARED_APP_SEQUENCE);
        END
/

-- --------------------------- INDEX CREATION -----------------------------
-- IDN_OAUTH2_ACCESS_TOKEN --
CREATE INDEX IDX_TC ON IDN_OAUTH2_ACCESS_TOKEN(TIME_CREATED)
  /
CREATE INDEX IDX_ATH ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN_HASH)
  /
CREATE INDEX IDX_AT_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN)
  /
CREATE INDEX IDX_AT_AT ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN)
  /
CREATE INDEX IDX_AT_RTH ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN_HASH)
  /
CREATE INDEX IDX_AT_RT ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN)
  /
CREATE INDEX IDX_AT_CKID_AU_TID_UD_TSH_TS ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE)
  /

  -- IDN_OAUTH2_AUTHORIZATION_CODE --
CREATE INDEX IDX_AUTHORIZATION_CODE_HASH ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHORIZATION_CODE_HASH, CONSUMER_KEY_ID)
  /
CREATE INDEX IDX_AUTHORIZATION_CODE_AU_TI ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER, TENANT_ID, USER_DOMAIN, STATE)
  /
CREATE INDEX IDX_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(CONSUMER_KEY_ID)
  /
CREATE INDEX IDX_AC_TID ON IDN_OAUTH2_AUTHORIZATION_CODE(TOKEN_ID)
  /
CREATE INDEX IDX_AC_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(AUTHORIZATION_CODE, CONSUMER_KEY_ID)
  /

  -- IDN_SCIM_GROUP --
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME)
  /

  -- IDN_AUTH_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED)
  /
CREATE INDEX IDX_IDN_AUTH_SSTR_ST_OP_ID_TM ON IDN_AUTH_SESSION_STORE (OPERATION, SESSION_TYPE, SESSION_ID, TIME_CREATED)
  /
CREATE INDEX IDX_IDN_AUTH_SSTR_ET_ID ON IDN_AUTH_SESSION_STORE (EXPIRY_TIME, SESSION_ID)
  /

  -- IDN_AUTH_TEMP_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED)
  /

  -- IDN_OAUTH2_SCOPE --
CREATE INDEX IDX_SC_TID ON IDN_OAUTH2_SCOPE(TENANT_ID)
  /

  -- IDN_OAUTH2_SCOPE_BINDING --
CREATE INDEX IDX_SB_SCPID ON IDN_OAUTH2_SCOPE_BINDING(SCOPE_ID)
  /

  -- IDN_OIDC_REQ_OBJECT_REFERENCE --
CREATE INDEX IDX_OROR_TID ON IDN_OIDC_REQ_OBJECT_REFERENCE(TOKEN_ID)
  /

  -- IDN_OAUTH2_ACCESS_TOKEN_SCOPE --
CREATE INDEX IDX_ATS_TID ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE(TOKEN_ID)
  /

  -- IDN_AUTH_USER --
CREATE INDEX IDX_AUTH_USER_UN_TID_DN ON IDN_AUTH_USER (USER_NAME, TENANT_ID, DOMAIN_NAME)
  /
CREATE INDEX IDX_AUTH_USER_DN_TOD ON IDN_AUTH_USER (DOMAIN_NAME, TENANT_ID)
  /

  -- IDN_AUTH_USER_SESSION_MAPPING --
CREATE INDEX IDX_USER_ID ON IDN_AUTH_USER_SESSION_MAPPING (USER_ID)
  /
CREATE INDEX IDX_SESSION_ID ON IDN_AUTH_USER_SESSION_MAPPING (SESSION_ID)
  /

  -- IDN_AUTH_SESSION_APP_INFO --
CREATE INDEX IDX_AUTH_SAI_UN_AID_SID ON IDN_AUTH_SESSION_APP_INFO (APP_ID, LOWER(SUBJECT), SESSION_ID)
  /

  -- IDN_OAUTH_CONSUMER_APPS --
CREATE INDEX IDX_OCA_UM_TID_UD_APN ON IDN_OAUTH_CONSUMER_APPS(USERNAME,TENANT_ID,USER_DOMAIN, APP_NAME)
  /

  -- IDX_SPI_APP --
CREATE INDEX IDX_SPI_APP ON SP_INBOUND_AUTH(APP_ID)
  /

  -- IDN_OIDC_PROPERTY --
CREATE INDEX IDX_IOP_CK ON IDN_OIDC_PROPERTY(CONSUMER_KEY);
  /

-- IDN_FIDO2_PROPERTY --
CREATE INDEX IDX_FIDO2_STR ON FIDO2_DEVICE_STORE(USER_NAME, TENANT_ID, DOMAIN_NAME, CREDENTIAL_ID, USER_HANDLE)
  /

-- IDN_ASSOCIATED_ID --
CREATE INDEX IDX_AI_DN_UN_AI ON IDN_ASSOCIATED_ID(DOMAIN_NAME, USER_NAME, ASSOCIATION_ID)
  /

-- IDN_OAUTH2_TOKEN_BINDING --
CREATE INDEX IDX_IDN_AUTH_BIND ON IDN_OAUTH2_TOKEN_BINDING (TOKEN_BINDING_REF)
  /
CREATE INDEX IDX_TK_VALUE_TYPE ON IDN_OAUTH2_TOKEN_BINDING (TOKEN_BINDING_VALUE, TOKEN_BINDING_TYPE)
/

-- IDN_FED_AUTH_SESSION_MAPPING --
CREATE INDEX IDX_FED_AUTH_SESSION_ID ON IDN_FED_AUTH_SESSION_MAPPING (SESSION_ID)
  /

-- SP_APP --
CREATE INDEX IDX_SP_APP_NAME_CI ON SP_APP (LOWER(APP_NAME))
  /

-- IDN_REMOTE_FETCH_REVISIONS --
CREATE INDEX IDX_REMOTE_FETCH_REVISION_CONFIG_ID ON IDN_REMOTE_FETCH_REVISIONS (CONFIG_ID)
/

-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_SP_APP_ID ON IDN_CORS_ASSOCIATION (SP_APP_ID)
/

-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_ORIGIN_ID ON IDN_CORS_ASSOCIATION (IDN_CORS_ORIGIN_ID)
/


-- UMA tables --
CREATE TABLE IDN_UMA_RESOURCE (
  ID                  INTEGER   NOT NULL,
  RESOURCE_ID         VARCHAR(255),
  RESOURCE_NAME       VARCHAR(255),
  TIME_CREATED        TIMESTAMP NOT NULL,
  RESOURCE_OWNER_NAME VARCHAR(255),
  CLIENT_ID           VARCHAR(255),
  TENANT_ID           INTEGER DEFAULT -1234,
  USER_DOMAIN         VARCHAR(50),
  PRIMARY KEY (ID)
)
/

CREATE SEQUENCE IDN_UMA_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_UMA_RESOURCE_TRIG NO CASCADE
BEFORE INSERT
  ON IDN_UMA_RESOURCE
  REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID) = (NEXTVAL FOR IDN_UMA_RESOURCE_SEQ);
  END
/

CREATE INDEX IDX_RID ON IDN_UMA_RESOURCE (RESOURCE_ID)
/

CREATE INDEX IDX_USER ON IDN_UMA_RESOURCE (RESOURCE_OWNER_NAME, USER_DOMAIN)
/

CREATE INDEX IDX_USER_RID ON IDN_UMA_RESOURCE (RESOURCE_ID, RESOURCE_OWNER_NAME, USER_DOMAIN, CLIENT_ID)
/

CREATE TABLE IDN_UMA_RESOURCE_META_DATA (
  ID                INTEGER NOT NULL,
  RESOURCE_IDENTITY INTEGER NOT NULL,
  PROPERTY_KEY      VARCHAR(40),
  PROPERTY_VALUE    VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
)
/

CREATE SEQUENCE IDN_UMA_RESOURCE_META_DATA_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_UMA_RESOURCE_META_DATA_TRIG NO CASCADE
BEFORE INSERT
  ON IDN_UMA_RESOURCE_META_DATA
  REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID) = (NEXTVAL FOR IDN_UMA_RESOURCE_META_DATA_SEQ);
  END
/

CREATE TABLE IDN_UMA_RESOURCE_SCOPE (
  ID                INTEGER NOT NULL,
  RESOURCE_IDENTITY INTEGER NOT NULL,
  SCOPE_NAME        VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
)
/

CREATE SEQUENCE IDN_UMA_RESOURCE_SCOPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_UMA_RESOURCE_SCOPE_TRIG  NO CASCADE
BEFORE INSERT
  ON IDN_UMA_RESOURCE_SCOPE
  REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID) = (NEXTVAL FOR IDN_UMA_RESOURCE_SCOPE_SEQ);
  END
/

CREATE INDEX IDX_RS ON IDN_UMA_RESOURCE_SCOPE (SCOPE_NAME)
/

CREATE TABLE IDN_UMA_PERMISSION_TICKET (
  ID              INTEGER      NOT NULL,
  PT              VARCHAR(255) NOT NULL,
  TIME_CREATED    TIMESTAMP    NOT NULL,
  EXPIRY_TIME     TIMESTAMP    NOT NULL,
  TICKET_STATE    VARCHAR(25) DEFAULT 'ACTIVE',
  TENANT_ID       INTEGER     DEFAULT -1234,
  TOKEN_ID        VARCHAR(255),
  PRIMARY KEY (ID)
)
/

CREATE SEQUENCE IDN_UMA_PERMISSION_TICKET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_UMA_PERMISSION_TICKET_TRIG NO CASCADE
BEFORE INSERT
  ON IDN_UMA_PERMISSION_TICKET
  REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID) = (NEXTVAL FOR IDN_UMA_PERMISSION_TICKET_SEQ);
  END
/

CREATE INDEX IDX_PT ON IDN_UMA_PERMISSION_TICKET (PT)
/

CREATE TABLE IDN_UMA_PT_RESOURCE (
  ID             INTEGER NOT NULL,
  PT_RESOURCE_ID INTEGER NOT NULL,
  PT_ID          INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_ID) REFERENCES IDN_UMA_PERMISSION_TICKET (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
)
/

CREATE SEQUENCE IDN_UMA_PT_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_UMA_PT_RESOURCE_TRIG NO CASCADE
BEFORE INSERT
  ON IDN_UMA_PT_RESOURCE
  REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID) = (NEXTVAL FOR IDN_UMA_PT_RESOURCE_SEQ);
  END
/

CREATE TABLE IDN_UMA_PT_RESOURCE_SCOPE (
  ID             INTEGER NOT NULL,
  PT_RESOURCE_ID INTEGER NOT NULL,
  PT_SCOPE_ID    INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_PT_RESOURCE (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_SCOPE_ID) REFERENCES IDN_UMA_RESOURCE_SCOPE (ID) ON DELETE CASCADE
)
/

CREATE SEQUENCE IDN_UMA_PT_RESOURCE_SCOPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER IDN_UMA_PT_RESOURCE_SCOPE_TRIG NO CASCADE
BEFORE INSERT
  ON IDN_UMA_PT_RESOURCE_SCOPE
  REFERENCING NEW AS NEW
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW.ID) = (NEXTVAL FOR IDN_UMA_PT_RESOURCE_SCOPE_SEQ);
  END
/

CREATE TABLE IDN_INVALID_TOKENS (
 	UUID VARCHAR(255) NOT NULL,
 	TOKEN_IDENTIFIER VARCHAR(2048) NOT NULL,
 	CONSUMER_KEY VARCHAR(255) NOT NULL,
 	TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	EXPIRY_TIMESTAMP TIMESTAMP NOT NULL,
 	PRIMARY KEY (UUID))
/

CREATE TABLE IDN_APP_REVOKED_EVENT (
	EVENT_ID VARCHAR(255) NOT NULL,
	CONSUMER_KEY VARCHAR(255) NOT NULL,
	TIME_REVOKED TIMESTAMP NOT NULL,
	ORGANIZATION VARCHAR(100) NOT NULL,
	PRIMARY KEY (EVENT_ID),
	CONSTRAINT CON_APP_EVT_KEY UNIQUE (CONSUMER_KEY, ORGANIZATION))
/

CREATE TABLE IDN_SUBJECT_ENTITY_REVOKED_EVENT (
	EVENT_ID VARCHAR(255) NOT NULL,
    ENTITY_ID VARCHAR(255) NOT NULL,
    ENTITY_TYPE VARCHAR(100) NOT NULL,
    TIME_REVOKED TIMESTAMP NOT NULL,
    ORGANIZATION VARCHAR(100) NOT NULL,
    PRIMARY KEY (EVENT_ID),
    CONSTRAINT CON_SUB_EVT_KEY UNIQUE (ENTITY_ID, ENTITY_TYPE, ORGANIZATION))
/

-- End of IDENTITY Tables--

-- Start of CONSENT-MGT Tables --

CREATE TABLE CM_PII_CATEGORY (
  ID           INTEGER NOT NULL ,
  NAME         VARCHAR(255) NOT NULL,
  DESCRIPTION  VARCHAR(1023),
  DISPLAY_NAME VARCHAR(255),
  IS_SENSITIVE INTEGER       NOT NULL,
  TENANT_ID    INTEGER DEFAULT -1234 NOT NULL,
  CONSTRAINT PII_CATEGORY_CONSTRAINT UNIQUE (NAME, TENANT_ID),
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE CM_PII_CATEGORY_SEQ
  START WITH 1
  INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER CM_PII_CATEGORY_TRIGGER NO CASCADE BEFORE INSERT ON CM_PII_CATEGORY
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR CM_PII_CATEGORY_SEQ);

END
/
CREATE TABLE CM_RECEIPT (
  CONSENT_RECEIPT_ID  VARCHAR(255) NOT NULL,
  VERSION             VARCHAR(255) NOT NULL,
  JURISDICTION        VARCHAR(255) NOT NULL,
  CONSENT_TIMESTAMP   TIMESTAMP     NOT NULL,
  COLLECTION_METHOD   VARCHAR(255) NOT NULL,
  LANGUAGE            VARCHAR(255) NOT NULL,
  PII_PRINCIPAL_ID    VARCHAR(255) NOT NULL,
  PRINCIPAL_TENANT_ID INTEGER DEFAULT -1234,
  POLICY_URL          VARCHAR(255) NOT NULL,
  STATE               VARCHAR(255) NOT NULL,
  PII_CONTROLLER      VARCHAR(2048) NOT NULL,
  PRIMARY KEY (CONSENT_RECEIPT_ID)
)
/
CREATE TABLE CM_PURPOSE (
  ID            INTEGER NOT NULL,
  NAME          VARCHAR(255) NOT NULL,
  DESCRIPTION   VARCHAR(1023),
  PURPOSE_GROUP VARCHAR(255) NOT NULL,
  GROUP_TYPE    VARCHAR(255) NOT NULL,
  TENANT_ID     INTEGER DEFAULT -1234 NOT NULL,
  CONSTRAINT PURPOSE_CONSTRAINT UNIQUE (NAME, TENANT_ID, PURPOSE_GROUP, GROUP_TYPE),
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE CM_PURPOSE_SEQ
  START WITH 1
  INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER CM_PURPOSE_TRIGGER NO CASCADE BEFORE INSERT ON CM_PURPOSE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR CM_PURPOSE_SEQ);

END
/
CREATE TABLE CM_PURPOSE_CATEGORY (
  ID          INTEGER NOT NULL,
  NAME        VARCHAR(255) NOT NULL,
  DESCRIPTION VARCHAR(1023),
  TENANT_ID   INTEGER DEFAULT -1234 NOT NULL,
  CONSTRAINT PURPOSE_CATEGORY_CONSTRAINT UNIQUE (NAME, TENANT_ID),
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE CM_PURPOSE_CATEGORY_SEQ
  START WITH 1
  INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER CM_PURPOSE_CATEGORY_TRIGGER NO CASCADE BEFORE INSERT ON CM_PURPOSE_CATEGORY
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR CM_PURPOSE_CATEGORY_SEQ);

END
/
CREATE TABLE CM_RECEIPT_SP_ASSOC (
  ID                 INTEGER NOT NULL,
  CONSENT_RECEIPT_ID VARCHAR(255) NOT NULL,
  SP_NAME            VARCHAR(255) NOT NULL,
  SP_DISPLAY_NAME    VARCHAR(255),
  SP_DESCRIPTION     VARCHAR(1024),
  SP_TENANT_ID       INTEGER DEFAULT -1234 NOT NULL,
  CONSTRAINT RECEIPT_SP_ASSOC_CONSTRAINT UNIQUE (CONSENT_RECEIPT_ID, SP_NAME, SP_TENANT_ID),
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE CM_RECEIPT_SP_ASSOC_SEQ
  START WITH 1
  INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER CM_RECEIPT_SP_ASSOC_TRIGGER NO CASCADE BEFORE INSERT ON CM_RECEIPT_SP_ASSOC
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR CM_RECEIPT_SP_ASSOC_SEQ);

END
/
CREATE TABLE CM_SP_PURPOSE_ASSOC (
  ID                     INTEGER NOT NULL,
  RECEIPT_SP_ASSOC       INTEGER       NOT NULL,
  PURPOSE_ID             INTEGER       NOT NULL,
  CONSENT_TYPE           VARCHAR(255) NOT NULL,
  IS_PRIMARY_PURPOSE     INTEGER       NOT NULL,
  TERMINATION            VARCHAR(255) NOT NULL,
  THIRD_PARTY_DISCLOSURE INTEGER       NOT NULL,
  THIRD_PARTY_NAME       VARCHAR(255),
  CONSTRAINT SP_PURPOSE_ASSOC UNIQUE (RECEIPT_SP_ASSOC, PURPOSE_ID),
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE CM_SP_PURPOSE_ASSOC_SEQ
  START WITH 1
  INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER CM_SP_PURPOSE_ASSOC_TRIGGER NO CASCADE BEFORE INSERT ON CM_SP_PURPOSE_ASSOC
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.ID)
       = (NEXTVAL FOR CM_SP_PURPOSE_ASSOC_SEQ);

END
/
CREATE TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC (
  ID                  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
  PURPOSE_CATEGORY_ID INTEGER NOT NULL,
  CONSTRAINT SP_PUS_PS_CAT_ASSOC UNIQUE (SP_PURPOSE_ASSOC_ID, PURPOSE_CATEGORY_ID),
  PRIMARY KEY (ID)
)
/
CREATE TABLE CM_PURPOSE_PII_CAT_ASSOC (
  ID                 INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  PURPOSE_ID         INTEGER NOT NULL,
  CM_PII_CATEGORY_ID INTEGER NOT NULL,
  IS_MANDATORY       INTEGER NOT NULL,
  CONSTRAINT PURPOSE_PII_CAT_ASSOC UNIQUE (PURPOSE_ID, CM_PII_CATEGORY_ID),
  PRIMARY KEY (ID)
)
/
CREATE TABLE CM_SP_PURPOSE_PII_CAT_ASSOC (
  ID                  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
  PII_CATEGORY_ID     INTEGER NOT NULL,
  VALIDITY            VARCHAR(1023),
  IS_CONSENTED        SMALLINT DEFAULT 1,
  CONSTRAINT SP_PURPOSE_PII_CATEGORY_ASSOC UNIQUE (SP_PURPOSE_ASSOC_ID, PII_CATEGORY_ID),
  PRIMARY KEY (ID)
)
/
CREATE SEQUENCE CM_SP_PURPOSE_PII_CAT_ASSOC_SEQ
  START WITH 1
  INCREMENT BY 1 NOCACHE
/
CREATE TABLE CM_CONSENT_RECEIPT_PROPERTY (
  ID                 INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  CONSENT_RECEIPT_ID VARCHAR(255)  NOT NULL,
  NAME               VARCHAR(255)  NOT NULL,
  VALUE              VARCHAR(1023) NOT NULL,
  CONSTRAINT CONSENT_RECEIPT_PROPERTY UNIQUE (CONSENT_RECEIPT_ID, NAME),
  PRIMARY KEY (ID)
)
/
ALTER TABLE CM_RECEIPT_SP_ASSOC
  ADD CONSTRAINT CM_RECEIPT_SP_ASSOC_fk0 FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID)
/
ALTER TABLE CM_SP_PURPOSE_ASSOC
  ADD CONSTRAINT CM_SP_PURPOSE_ASSOC_fk0 FOREIGN KEY (RECEIPT_SP_ASSOC) REFERENCES CM_RECEIPT_SP_ASSOC (ID)
/
ALTER TABLE CM_SP_PURPOSE_ASSOC
  ADD CONSTRAINT CM_SP_PURPOSE_ASSOC_fk1 FOREIGN KEY (PURPOSE_ID) REFERENCES CM_PURPOSE (ID)
/
ALTER TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC
  ADD CONSTRAINT CM_SP_P_P_CAT_ASSOC_fk0 FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID)
/
ALTER TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC
  ADD CONSTRAINT CM_SP_P_P_CAT_ASSOC_fk1 FOREIGN KEY (PURPOSE_CATEGORY_ID) REFERENCES CM_PURPOSE_CATEGORY (ID)
/
ALTER TABLE CM_SP_PURPOSE_PII_CAT_ASSOC
  ADD CONSTRAINT CM_SP_P_PII_CAT_ASSOC_fk0 FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID)
/
ALTER TABLE CM_SP_PURPOSE_PII_CAT_ASSOC
  ADD CONSTRAINT CM_SP_P_PII_CAT_ASSOC_fk1 FOREIGN KEY (PII_CATEGORY_ID) REFERENCES CM_PII_CATEGORY (ID)
/
ALTER TABLE CM_CONSENT_RECEIPT_PROPERTY
  ADD CONSTRAINT CM_CONSENT_RECEIPT_PRT_fk0 FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID)
/
INSERT INTO CM_PURPOSE (NAME, DESCRIPTION, PURPOSE_GROUP, GROUP_TYPE, TENANT_ID) VALUES ('DEFAULT', 'For core functionalities of the product', 'DEFAULT', 'SP', '-1234')/

INSERT INTO CM_PURPOSE_CATEGORY (NAME, DESCRIPTION, TENANT_ID) VALUES ('DEFAULT','For core functionalities of the product', '-1234')/
-- End of CONSENT-MGT Tables --

-- Start of API-MGT Tables --
CREATE TABLE AM_SUBSCRIBER (
            SUBSCRIBER_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            USER_ID VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            EMAIL_ADDRESS VARCHAR(256) NULL,
            DATE_SUBSCRIBED TIMESTAMP NOT NULL,
            PRIMARY KEY (SUBSCRIBER_ID),
            CREATED_BY VARCHAR(100),
            CREATED_TIME TIMESTAMP,
            UPDATED_BY VARCHAR(100),
            UPDATED_TIME TIMESTAMP,
            UNIQUE (TENANT_ID,USER_ID)
)/

CREATE TABLE AM_APPLICATION (
            APPLICATION_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            NAME VARCHAR(100) NOT NULL,
            SUBSCRIBER_ID INTEGER NOT NULL,
            APPLICATION_TIER VARCHAR(50) DEFAULT 'Unlimited',
            CALLBACK_URL VARCHAR(512),
            DESCRIPTION VARCHAR(512),
            APPLICATION_STATUS VARCHAR(50) DEFAULT 'APPROVED',
            GROUP_ID VARCHAR(100),
            CREATED_BY VARCHAR(100),
            CREATED_TIME TIMESTAMP,
            UPDATED_BY VARCHAR(100),
            UPDATED_TIME TIMESTAMP,
            UUID VARCHAR(256),
            TOKEN_TYPE VARCHAR(100),
            ORGANIZATION VARCHAR(100) NOT NULL,
            FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE RESTRICT,
            PRIMARY KEY(APPLICATION_ID),
            UNIQUE (NAME,SUBSCRIBER_ID,ORGANIZATION)
)/

CREATE TABLE AM_MONETIZATION_USAGE (
	ID VARCHAR(100) NOT NULL,
	STATE VARCHAR(50) NOT NULL,
	STATUS VARCHAR(50) NOT NULL,
	STARTED_TIME VARCHAR(50) NOT NULL,
	PUBLISHED_TIME VARCHAR(50) NOT NULL,
	PRIMARY KEY(ID)
)/

CREATE TABLE AM_API (
            API_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            API_UUID VARCHAR(256) NOT NULL,
            API_PROVIDER VARCHAR(200) NOT NULL,
            API_NAME VARCHAR(200) NOT NULL,
            API_VERSION VARCHAR(30) NOT NULL,
            CONTEXT VARCHAR(256),
            CONTEXT_TEMPLATE VARCHAR(256),
            API_TIER VARCHAR(256),
            API_TYPE VARCHAR(10),
            ORGANIZATION VARCHAR(100) NOT NULL,
            GATEWAY_VENDOR VARCHAR(100) DEFAULT 'wso2',
            CREATED_BY VARCHAR(100),
            CREATED_TIME TIMESTAMP,
            UPDATED_BY VARCHAR(100),
            UPDATED_TIME TIMESTAMP,
            REVISIONS_CREATED INTEGER DEFAULT 0,
            PRIMARY KEY(API_ID),
            STATUS VARCHAR(30),
            VERSION_COMPARABLE VARCHAR(15),
            LOG_LEVEL VARCHAR(255) DEFAULT 'OFF',
            UNIQUE (API_UUID),
            UNIQUE (API_PROVIDER,API_NAME,API_VERSION,ORGANIZATION)
)/

CREATE TABLE AM_GRAPHQL_COMPLEXITY (
    UUID VARCHAR(256) NOT NULL,
    API_ID INTEGER NOT NULL,
    TYPE VARCHAR(256) NOT NULL,
    FIELD VARCHAR(256) NOT NULL,
    COMPLEXITY_VALUE INTEGER,
    REVISION_UUID VARCHAR(256),
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    PRIMARY KEY(UUID)
)/

CREATE TABLE AM_API_URL_MAPPING (
            URL_MAPPING_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            API_ID INTEGER NOT NULL,
            HTTP_METHOD VARCHAR(20) NULL,
            AUTH_SCHEME VARCHAR(50) NULL,
            URL_PATTERN VARCHAR(512) NULL,
            THROTTLING_TIER varchar(512) DEFAULT NULL,
            MEDIATION_SCRIPT BLOB,
            REVISION_UUID VARCHAR(255),
            LOG_LEVEL VARCHAR(255) DEFAULT 'OFF',
            PRIMARY KEY (URL_MAPPING_ID)
)/

CREATE TABLE AM_API_RESOURCE_SCOPE_MAPPING (
            SCOPE_NAME VARCHAR(255) NOT NULL,
            URL_MAPPING_ID INTEGER NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
            PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
)/

CREATE TABLE AM_SECURITY_AUDIT_UUID_MAPPING (
    API_ID INTEGER NOT NULL,
    AUDIT_UUID VARCHAR(255) NOT NULL,
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    PRIMARY KEY (API_ID)
)/

CREATE TABLE AM_API_PRODUCT_MAPPING (
	API_PRODUCT_MAPPING_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
	API_ID INTEGER,
	URL_MAPPING_ID INTEGER,
	REVISION_UUID VARCHAR(255),
	FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
	FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
	PRIMARY KEY(API_PRODUCT_MAPPING_ID)
)/

CREATE TABLE AM_SUBSCRIPTION (
            SUBSCRIPTION_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            TIER_ID VARCHAR(50),
            TIER_ID_PENDING VARCHAR(50),
            API_ID INTEGER,
            LAST_ACCESSED TIMESTAMP NULL,
            APPLICATION_ID INTEGER,
            SUB_STATUS VARCHAR(50),
            SUBS_CREATE_STATE VARCHAR(50) DEFAULT 'SUBSCRIBE',
            CREATED_BY VARCHAR(100),
            CREATED_TIME TIMESTAMP,
            UPDATED_BY VARCHAR(100),
            UPDATED_TIME TIMESTAMP,
            UUID VARCHAR(256) NOT NULL,
            FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
            FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE RESTRICT,
            PRIMARY KEY (SUBSCRIPTION_ID),
            UNIQUE (UUID)
)/

CREATE TABLE AM_APPLICATION_KEY_MAPPING (
            UUID VARCHAR(100),
            APPLICATION_ID INTEGER NOT NULL,
            CONSUMER_KEY VARCHAR(255),
            KEY_TYPE VARCHAR(512) NOT NULL,
            STATE VARCHAR(30) NOT NULL,
            CREATE_MODE VARCHAR(30) DEFAULT 'CREATED',
            KEY_MANAGER VARCHAR(100) NOT NULL,
            APP_INFO BLOB DEFAULT NULL,
            FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
            PRIMARY KEY(APPLICATION_ID,KEY_TYPE,KEY_MANAGER)
)/

CREATE TABLE AM_API_LC_EVENT (
            EVENT_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            API_ID INTEGER NOT NULL,
            PREVIOUS_STATE VARCHAR(50),
            NEW_STATE VARCHAR(50) NOT NULL,
            USER_ID VARCHAR(255) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            EVENT_DATE TIMESTAMP NOT NULL,
            FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
            PRIMARY KEY (EVENT_ID)
)/

CREATE TABLE AM_APP_KEY_DOMAIN_MAPPING (
            CONSUMER_KEY VARCHAR(255) NOT NULL,
            AUTHZ_DOMAIN VARCHAR(255) NOT NULL DEFAULT 'ALL',
            PRIMARY KEY (CONSUMER_KEY,AUTHZ_DOMAIN)
)/

CREATE TABLE AM_API_COMMENTS (
            COMMENT_ID VARCHAR(64) NOT NULL,
            COMMENT_TEXT VARCHAR(512),
            CREATED_BY VARCHAR(255),
            CREATED_TIME TIMESTAMP NOT NULL,
            UPDATED_TIME TIMESTAMP,
            API_ID INTEGER,
            PARENT_COMMENT_ID VARCHAR(64) DEFAULT NULL,
            ENTRY_POINT VARCHAR(20),
            CATEGORY VARCHAR(20) DEFAULT 'general',
            FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID),
            FOREIGN KEY(PARENT_COMMENT_ID) REFERENCES AM_API_COMMENTS(COMMENT_ID),
            PRIMARY KEY (COMMENT_ID)
)/

CREATE TABLE AM_API_RATINGS (
            RATING_ID VARCHAR(255) NOT NULL,
            API_ID INTEGER,
            RATING INTEGER,
            SUBSCRIBER_ID INTEGER,
            FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
            FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE CASCADE,
            PRIMARY KEY (RATING_ID)
)/

CREATE TABLE AM_TIER_PERMISSIONS (
            TIER_PERMISSIONS_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            TIER VARCHAR(50) NOT NULL,
            PERMISSIONS_TYPE VARCHAR(50) NOT NULL,
            ROLES VARCHAR(512) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            PRIMARY KEY(TIER_PERMISSIONS_ID)
)/

CREATE TABLE AM_EXTERNAL_STORES (
            APISTORE_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            API_ID INTEGER,
            STORE_ID VARCHAR(255) NOT NULL,
            STORE_DISPLAY_NAME VARCHAR(255) NOT NULL,
            STORE_ENDPOINT VARCHAR(255) NOT NULL,
            STORE_TYPE VARCHAR(255) NOT NULL,
            LAST_UPDATED_TIME TIMESTAMP,
            FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE RESTRICT,
            PRIMARY KEY (APISTORE_ID)
)
/

CREATE TABLE AM_WORKFLOWS(
            WF_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            WF_REFERENCE VARCHAR(255) NOT NULL,
            WF_TYPE VARCHAR(255) NOT NULL,
            WF_STATUS VARCHAR(255) NOT NULL,
            WF_CREATED_TIME TIMESTAMP,
            WF_UPDATED_TIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
            WF_STATUS_DESC VARCHAR(1000),
            TENANT_ID INTEGER,
            TENANT_DOMAIN VARCHAR(255),
            WF_EXTERNAL_REFERENCE VARCHAR(255) NOT NULL,
            WF_METADATA BLOB DEFAULT NULL,
            WF_PROPERTIES BLOB DEFAULT NULL,
            PRIMARY KEY (WF_ID),
            UNIQUE (WF_EXTERNAL_REFERENCE)
)/

CREATE TABLE AM_APPLICATION_REGISTRATION (
            REG_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            SUBSCRIBER_ID INT NOT NULL,
            WF_REF VARCHAR(255) NOT NULL,
            APP_ID INT NOT NULL,
            TOKEN_TYPE VARCHAR(30) NOT NULL,
            TOKEN_SCOPE VARCHAR(1500) DEFAULT 'default',
            INPUTS VARCHAR(1000),
            ALLOWED_DOMAINS VARCHAR(256),
            VALIDITY_PERIOD BIGINT,
            KEY_MANAGER VARCHAR(255) NOT NULL,
            UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER),
            FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE RESTRICT,
            FOREIGN KEY(APP_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE,
            PRIMARY KEY (REG_ID)
)/

CREATE TABLE AM_SHARED_SCOPE (
    NAME VARCHAR(255),
    UUID VARCHAR (256) NOT NULL,
    TENANT_ID INTEGER,
    PRIMARY KEY (UUID)
)/

CREATE TABLE AM_API_DEFAULT_VERSION (
            DEFAULT_VERSION_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            API_NAME VARCHAR(256) NOT NULL ,
            API_PROVIDER VARCHAR(256) NOT NULL ,
            DEFAULT_API_VERSION VARCHAR(30) ,
            PUBLISHED_DEFAULT_API_VERSION VARCHAR(30) ,
            ORGANIZATION VARCHAR(100),
            PRIMARY KEY (DEFAULT_VERSION_ID)
)/

CREATE INDEX IDX_SUB_APP_ID ON AM_SUBSCRIPTION (APPLICATION_ID, SUBSCRIPTION_ID)/

CREATE TABLE AM_ALERT_TYPES (
            ALERT_TYPE_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            ALERT_TYPE_NAME VARCHAR(256) NOT NULL ,
	    STAKE_HOLDER VARCHAR(100) NOT NULL,
            PRIMARY KEY (ALERT_TYPE_ID)
)/

CREATE TABLE AM_ALERT_TYPES_VALUES (
            ALERT_TYPE_ID INTEGER NOT NULL,
            USER_NAME VARCHAR(256) NOT NULL ,
	    STAKE_HOLDER VARCHAR(100) NOT NULL ,
	        PRIMARY KEY (ALERT_TYPE_ID,USER_NAME,STAKE_HOLDER)
)/

CREATE TABLE AM_ALERT_EMAILLIST (
	    EMAIL_LIST_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            USER_NAME VARCHAR(256) NOT NULL ,
	    STAKE_HOLDER VARCHAR(100) NOT NULL ,
            PRIMARY KEY (EMAIL_LIST_ID),
    	    CONSTRAINT AM_ALERT_EMAILLIST_CONSTRAINT UNIQUE (EMAIL_LIST_ID,USER_NAME,STAKE_HOLDER)
)/

CREATE TABLE AM_ALERT_EMAILLIST_DETAILS (
            EMAIL_LIST_ID INTEGER NOT NULL,
	    EMAIL VARCHAR(255) NOT NULL,
	        PRIMARY KEY (EMAIL_LIST_ID,EMAIL)
)/

INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalResponseTime', 'publisher')/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalBackendTime', 'publisher')/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalRequestsPerMin', 'subscriber')/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalRequestPattern', 'subscriber')/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('UnusualIPAccess', 'subscriber')/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('FrequentTierLimitHitting', 'subscriber')/
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('ApiHealthMonitor', 'publisher')/



-- AM Throttling tables --

CREATE TABLE AM_POLICY_SUBSCRIPTION (
            POLICY_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            NAME VARCHAR(512) NOT NULL,
            DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
            TENANT_ID INT NOT NULL,
            DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
            QUOTA_TYPE VARCHAR(25) NOT NULL,
            QUOTA INT NOT NULL,
            QUOTA_UNIT VARCHAR(10) NULL,
            UNIT_TIME INT NOT NULL,
            TIME_UNIT VARCHAR(25) NOT NULL,
            RATE_LIMIT_COUNT INT NULL DEFAULT NULL,
            RATE_LIMIT_TIME_UNIT VARCHAR(25) NULL DEFAULT NULL,
            IS_DEPLOYED SMALLINT NOT NULL DEFAULT 0,
	          CUSTOM_ATTRIBUTES BLOB DEFAULT NULL,
            STOP_ON_QUOTA_REACH SMALLINT NOT NULL DEFAULT 0,
            BILLING_PLAN VARCHAR(20) NOT NULL,
            UUID VARCHAR(256) NOT NULL,
            MONETIZATION_PLAN VARCHAR(25) DEFAULT NULL,
            FIXED_RATE VARCHAR(15) DEFAULT NULL,
            BILLING_CYCLE VARCHAR(15) DEFAULT NULL,
            PRICE_PER_REQUEST VARCHAR(15) DEFAULT NULL,
            CURRENCY VARCHAR(15) DEFAULT NULL,
            MAX_COMPLEXITY INT NOT NULL DEFAULT 0,
            MAX_DEPTH INT NOT NULL DEFAULT 0,
            CONNECTIONS_COUNT INT NOT NULL DEFAULT 0,
            PRIMARY KEY (POLICY_ID),
            CONSTRAINT AM_POLICY_SUBSCRIPTION_NAME_TENANT UNIQUE (NAME, TENANT_ID),
            UNIQUE (UUID)
)/

CREATE TABLE AM_POLICY_APPLICATION (
            POLICY_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            NAME VARCHAR(512) NOT NULL,
            DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
            TENANT_ID INT NOT NULL,
            DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
            QUOTA_TYPE VARCHAR(25) NOT NULL,
            QUOTA INT NOT NULL,
            QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
            UNIT_TIME INT NOT NULL,
            TIME_UNIT VARCHAR(25) NOT NULL,
            IS_DEPLOYED SMALLINT NOT NULL DEFAULT 0,
	          CUSTOM_ATTRIBUTES BLOB DEFAULT NULL,
            RATE_LIMIT_COUNT INT NULL DEFAULT 0,
            RATE_LIMIT_TIME_UNIT VARCHAR(25) NULL DEFAULT NULL,
	          UUID VARCHAR(256) NOT NULL,
            PRIMARY KEY (POLICY_ID),
            CONSTRAINT APP_NAME_TENANT UNIQUE (NAME, TENANT_ID),
            UNIQUE (UUID)
)/

CREATE TABLE AM_POLICY_HARD_THROTTLING (
            POLICY_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            NAME VARCHAR(512) NOT NULL,
            TENANT_ID INT NOT NULL,
            DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
            QUOTA_TYPE VARCHAR(25) NOT NULL,
            QUOTA INT NOT NULL,
            QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
            UNIT_TIME INT NOT NULL,
            TIME_UNIT VARCHAR(25) NOT NULL,
            IS_DEPLOYED SMALLINT NOT NULL DEFAULT 0,
            PRIMARY KEY (POLICY_ID),
            CONSTRAINT POLICY_HARD_NAME_TENANT UNIQUE (NAME, TENANT_ID)
)/


CREATE TABLE AM_API_THROTTLE_POLICY (
            POLICY_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            NAME VARCHAR(512) NOT NULL,
            DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
            TENANT_ID INT NOT NULL,
            DESCRIPTION VARCHAR (1024),
            DEFAULT_QUOTA_TYPE VARCHAR(25) NOT NULL,
            DEFAULT_QUOTA INTEGER NOT NULL,
            DEFAULT_QUOTA_UNIT VARCHAR(10) NULL,
            DEFAULT_UNIT_TIME INTEGER NOT NULL,
            DEFAULT_TIME_UNIT VARCHAR(25) NOT NULL,
            APPLICABLE_LEVEL VARCHAR(25) NOT NULL,
            IS_DEPLOYED SMALLINT NOT NULL DEFAULT 0,
            UUID VARCHAR(256) NOT NULL,
            PRIMARY KEY (POLICY_ID),
            CONSTRAINT API_NAME_TENANT UNIQUE (NAME, TENANT_ID),
            UNIQUE (UUID)
)/

CREATE TABLE AM_CONDITION_GROUP (
            CONDITION_GROUP_ID INTEGER NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            POLICY_ID INTEGER NOT NULL,
            QUOTA_TYPE VARCHAR(25),
            QUOTA INTEGER NOT NULL,
            QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
            UNIT_TIME INTEGER NOT NULL,
            TIME_UNIT VARCHAR(25) NOT NULL,
            DESCRIPTION VARCHAR (1024) NULL DEFAULT NULL,
            PRIMARY KEY (CONDITION_GROUP_ID),
            FOREIGN KEY (POLICY_ID) REFERENCES AM_API_THROTTLE_POLICY(POLICY_ID) ON DELETE CASCADE
)/

CREATE TABLE AM_QUERY_PARAMETER_CONDITION (
            QUERY_PARAMETER_ID INTEGER NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            CONDITION_GROUP_ID INTEGER NOT NULL,
            PARAMETER_NAME VARCHAR(255) DEFAULT NULL,
            PARAMETER_VALUE VARCHAR(255) DEFAULT NULL,
	    	IS_PARAM_MAPPING SMALLINT DEFAULT 1,
            PRIMARY KEY (QUERY_PARAMETER_ID),
            FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE
)/

CREATE TABLE AM_HEADER_FIELD_CONDITION (
            HEADER_FIELD_ID INTEGER NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            CONDITION_GROUP_ID INTEGER NOT NULL,
            HEADER_FIELD_NAME VARCHAR(255) DEFAULT NULL,
            HEADER_FIELD_VALUE VARCHAR(255) DEFAULT NULL,
	    	IS_HEADER_FIELD_MAPPING SMALLINT DEFAULT 1,
            PRIMARY KEY (HEADER_FIELD_ID),
            FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE
)/

CREATE TABLE AM_JWT_CLAIM_CONDITION (
            JWT_CLAIM_ID INTEGER NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            CONDITION_GROUP_ID INTEGER NOT NULL,
            CLAIM_URI VARCHAR(512) DEFAULT NULL,
            CLAIM_ATTRIB VARCHAR(1024) DEFAULT NULL,
	    IS_CLAIM_MAPPING SMALLINT DEFAULT 1,
            PRIMARY KEY (JWT_CLAIM_ID),
            FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE
)/

CREATE TABLE AM_IP_CONDITION (
  AM_IP_CONDITION_ID INT NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  STARTING_IP VARCHAR(45) NULL,
  ENDING_IP VARCHAR(45) NULL,
  SPECIFIC_IP VARCHAR(45) NULL,
  WITHIN_IP_RANGE SMALLINT DEFAULT 1,
  CONDITION_GROUP_ID INT NULL,
  PRIMARY KEY (AM_IP_CONDITION_ID),
  CONSTRAINT fk_AM_IP_CONDITION_1    FOREIGN KEY (CONDITION_GROUP_ID)
  REFERENCES AM_CONDITION_GROUP (CONDITION_GROUP_ID)   ON DELETE CASCADE)
/

CREATE INDEX fk_AM_IP_CONDITION_1_idx ON AM_IP_CONDITION (CONDITION_GROUP_ID)
/

CREATE TABLE AM_POLICY_GLOBAL (
            POLICY_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            NAME VARCHAR(512) NOT NULL,
            KEY_TEMPLATE VARCHAR(512) NOT NULL,
            TENANT_ID INT NOT NULL,
            DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
            SIDDHI_QUERY BLOB DEFAULT NULL,
            IS_DEPLOYED SMALLINT NOT NULL DEFAULT 0,
            UUID VARCHAR(256) NOT NULL,
            PRIMARY KEY (POLICY_ID),
            UNIQUE (UUID)
)/

CREATE TABLE AM_THROTTLE_TIER_PERMISSIONS (
  THROTTLE_TIER_PERMISSIONS_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  TIER VARCHAR(50) NULL,
  PERMISSIONS_TYPE VARCHAR(50) NULL,
  ROLES VARCHAR(512) NULL,
  TENANT_ID INT NULL,
  PRIMARY KEY (THROTTLE_TIER_PERMISSIONS_ID))
/

CREATE TABLE AM_BLOCK_CONDITIONS (
  CONDITION_ID INT NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  TYPE varchar(45) DEFAULT NULL,
  BLOCK_CONDITION varchar(512) DEFAULT NULL,
  ENABLED varchar(45) DEFAULT NULL,
  DOMAIN varchar(45) DEFAULT NULL,
  UUID VARCHAR(256) NOT NULL,
  PRIMARY KEY (CONDITION_ID),
  UNIQUE (UUID)
) /

CREATE TABLE AM_CERTIFICATE_METADATA (
  TENANT_ID INT NOT NULL,
  ALIAS VARCHAR(255) NOT NULL,
  END_POINT VARCHAR(255) NOT NULL,
  CERTIFICATE BLOB DEFAULT NULL,
  CONSTRAINT PK_ALIAS PRIMARY KEY (ALIAS)
) /

CREATE TABLE AM_API_CLIENT_CERTIFICATE (
  TENANT_ID INT NOT NULL,
  ALIAS VARCHAR(45) NOT NULL,
  API_ID INTEGER NOT NULL,
  CERTIFICATE BLOB NOT NULL,
  REMOVED SMALLINT NOT NULL DEFAULT 0,
  TIER_NAME VARCHAR (512),
  REVISION_UUID VARCHAR(255) NOT NULL DEFAULT 'Current API',
  FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE,
  PRIMARY KEY (ALIAS, TENANT_ID, REMOVED, REVISION_UUID)
)/

CREATE TABLE AM_APPLICATION_GROUP_MAPPING (
    APPLICATION_ID INT NOT NULL,
    GROUP_ID VARCHAR(512) NOT NULL,
    TENANT VARCHAR(255) NOT NULL,
    PRIMARY KEY (APPLICATION_ID,GROUP_ID,TENANT),
    FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE
) /

CREATE TABLE AM_USAGE_UPLOADED_FILES (
  TENANT_DOMAIN varchar(255) NOT NULL,
  FILE_NAME varchar(255) NOT NULL,
  FILE_TIMESTAMP TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FILE_PROCESSED SMALLINT NOT NULL DEFAULT 0,
  FILE_CONTENT BLOB DEFAULT NULL,
  PRIMARY KEY (TENANT_DOMAIN, FILE_NAME, FILE_TIMESTAMP)
) /

CREATE TABLE AM_API_LC_PUBLISH_EVENTS (
    ID INTEGER NOT NULL,
    TENANT_DOMAIN VARCHAR(500) NOT NULL,
    API_ID VARCHAR(500) NOT NULL,
    EVENT_TIME TIMESTAMP(0) NOT NULL,
    PRIMARY KEY (ID)
) /

CREATE TABLE AM_APPLICATION_ATTRIBUTES (
  APPLICATION_ID INTEGER NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  APP_ATTRIBUTE VARCHAR(1024) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (APPLICATION_ID,NAME),
  FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION (APPLICATION_ID) ON DELETE CASCADE
) /

CREATE TABLE AM_SYSTEM_APPS (
    ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    NAME VARCHAR(50) NOT NULL,
    CONSUMER_KEY VARCHAR(512) NOT NULL,
    CONSUMER_SECRET VARCHAR(512) NOT NULL,
    TENANT_DOMAIN VARCHAR(255) DEFAULT 'carbon.super',
    CREATED_TIME TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (NAME),
    UNIQUE (CONSUMER_KEY),
    PRIMARY KEY (ID)
) /

--  BotDATA Email table --
CREATE TABLE AM_NOTIFICATION_SUBSCRIBER (
    UUID VARCHAR(255) NOT NULL,
    CATEGORY VARCHAR(255) NOT NULL,
    NOTIFICATION_METHOD VARCHAR(255) NOT NULL,
    SUBSCRIBER_ADDRESS VARCHAR(255) NOT NULL,
    PRIMARY KEY(UUID, SUBSCRIBER_ADDRESS)
) /

CREATE TABLE AM_REVOKED_JWT (
    UUID VARCHAR(255) NOT NULL,
    SIGNATURE VARCHAR(2048) NOT NULL,
    EXPIRY_TIMESTAMP BIGINT NOT NULL,
    TENANT_ID INTEGER DEFAULT -1,
    TOKEN_TYPE VARCHAR(15) DEFAULT 'DEFAULT',
    TIME_CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (UUID)
); /

CREATE TABLE AM_USER (
    USER_ID VARCHAR(255) NOT NULL,
    USER_NAME VARCHAR(255) NOT NULL,
    PRIMARY KEY(USER_ID)
);/

CREATE TABLE AM_KEY_MANAGER (
  UUID VARCHAR(50) NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  DISPLAY_NAME VARCHAR(100) NULL,
  DESCRIPTION VARCHAR(256) NULL,
  TYPE VARCHAR(45) NULL,
  CONFIGURATION BLOB NULL,
  ENABLED SMALLINT DEFAULT 1,
  ORGANIZATION VARCHAR(100) NOT NULL,
  TOKEN_TYPE VARCHAR(45) NULL,
  EXTERNAL_REFERENCE_ID VARCHAR(100) NULL,
  PRIMARY KEY (UUID),
  UNIQUE (NAME,ORGANIZATION)
)
 /

CREATE TABLE AM_KEY_MANAGER_PERMISSIONS (
  KEY_MANAGER_UUID VARCHAR(50) NOT NULL,
  PERMISSIONS_TYPE VARCHAR(50) NOT NULL,
  ROLE VARCHAR(255) NOT NULL,
  PRIMARY KEY (KEY_MANAGER_UUID, ROLE),
  FOREIGN KEY (KEY_MANAGER_UUID) REFERENCES AM_KEY_MANAGER(UUID) ON DELETE CASCADE
)
 /

CREATE TABLE AM_API_CATEGORIES (
  UUID VARCHAR(50) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  DESCRIPTION VARCHAR(1024),
  ORGANIZATION VARCHAR(100) NOT NULL,
  UNIQUE (NAME,ORGANIZATION),
  PRIMARY KEY (UUID)
) /

-- AM_GW_PUBLISHED_API_DETAILS & AM_GW_API_ARTIFACTS are independent tables for Artifact synchronizer feature which --
-- should not have any referential integrity constraints with other tables in AM database--

CREATE TABLE AM_GW_PUBLISHED_API_DETAILS (
  API_ID varchar(255) NOT NULL,
  TENANT_DOMAIN varchar(255),
  API_PROVIDER varchar(255),
  API_NAME varchar(255),
  API_VERSION varchar(255),
  API_TYPE varchar(50),
  PRIMARY KEY (API_ID)
) /

CREATE TABLE AM_GW_API_ARTIFACTS (
  API_ID varchar(255) NOT NULL,
  REVISION_ID varchar(255) NOT NULL,
  ARTIFACT blob,
  TIME_STAMP TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
  PRIMARY KEY (REVISION_ID, API_ID),
  FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS (API_ID) ON DELETE NO ACTION ON UPDATE RESTRICT)
   /

CREATE TABLE AM_GW_API_DEPLOYMENTS (
  API_ID VARCHAR(255) NOT NULL,
  REVISION_ID VARCHAR(255) NOT NULL,
  LABEL VARCHAR(255) NOT NULL,
  VHOST VARCHAR(255) NULL,
  PRIMARY KEY (REVISION_ID, API_ID,LABEL),
  FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON DELETE CASCADE
)
   /

-- Tenant Themes Table --
CREATE TABLE AM_TENANT_THEMES (
  TENANT_ID INTEGER NOT NULL,
  THEME BLOB NOT NULL,
  PRIMARY KEY (TENANT_ID)
) /

CREATE TABLE AM_SCOPE (
            SCOPE_ID INTEGER NOT NULL,
            NAME VARCHAR(255) NOT NULL,
            DISPLAY_NAME VARCHAR(255) NOT NULL,
            DESCRIPTION VARCHAR(512),
            TENANT_ID INTEGER NOT NULL DEFAULT -1,
            SCOPE_TYPE VARCHAR(255) NOT NULL,
            PRIMARY KEY (SCOPE_ID))
/
CREATE SEQUENCE AM_SCOPE_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER AM_SCOPE_TRIGGER NO CASCADE BEFORE INSERT ON AM_SCOPE
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.SCOPE_ID)
       = (NEXTVAL FOR AM_SCOPE_SEQUENCE);

END
/
CREATE TABLE AM_SCOPE_BINDING (
            SCOPE_ID INTEGER NOT NULL,
            SCOPE_BINDING VARCHAR(255) NOT NULL,
            BINDING_TYPE VARCHAR(255) NOT NULL,
            FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE)
/

CREATE TABLE AM_REVISION (
            ID INTEGER NOT NULL,
            API_UUID VARCHAR(256) NOT NULL,
            REVISION_UUID VARCHAR(255) NOT NULL,
            DESCRIPTION VARCHAR(255),
            CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CREATED_BY VARCHAR(255),
            PRIMARY KEY (ID, API_UUID),
            UNIQUE(REVISION_UUID))
/

CREATE TABLE AM_API_REVISION_METADATA (
            API_UUID VARCHAR(64) NOT NULL,
            REVISION_UUID VARCHAR(255) NOT NULL,
            API_TIER VARCHAR(128),
            UNIQUE (API_UUID,REVISION_UUID),
            FOREIGN KEY(REVISION_UUID) REFERENCES AM_REVISION(REVISION_UUID) ON DELETE CASCADE
)/

CREATE TABLE AM_DEPLOYMENT_REVISION_MAPPING (
            NAME VARCHAR(255) NOT NULL,
            VHOST VARCHAR(255) NULL,
            REVISION_UUID VARCHAR(255) NOT NULL,
            REVISION_STATUS VARCHAR(255) NULL,
            DISPLAY_ON_DEVPORTAL SMALLINT DEFAULT 0,
            DEPLOYED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (NAME, REVISION_UUID),
            FOREIGN KEY (REVISION_UUID) REFERENCES AM_REVISION(REVISION_UUID) ON DELETE CASCADE)
/

CREATE TABLE AM_DEPLOYED_REVISION (
            NAME VARCHAR(255) NOT NULL,
            VHOST VARCHAR(255) NULL,
            REVISION_UUID VARCHAR(255) NOT NULL,
            DEPLOYED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (NAME, REVISION_UUID),
            FOREIGN KEY (REVISION_UUID) REFERENCES AM_REVISION(REVISION_UUID) ON DELETE CASCADE)
/

-- Gateway Environments Table --
CREATE TABLE AM_GATEWAY_ENVIRONMENT (
            ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
            UUID VARCHAR(45) NOT NULL,
            NAME VARCHAR(255) NOT NULL,
            TYPE VARCHAR(255) NULL,
            DISPLAY_NAME VARCHAR(255) NULL,
            DESCRIPTION VARCHAR(1023) NULL,
            PROVIDER VARCHAR(255) NOT NULL,
            GATEWAY_TYPE VARCHAR(255) NOT NULL,
            ORGANIZATION  VARCHAR(255) NOT NULL,
            UNIQUE (NAME, ORGANIZATION),
            UNIQUE (UUID),
            PRIMARY KEY (ID))
/

-- Virtual Hosts Table --
CREATE TABLE AM_GW_VHOST (
            GATEWAY_ENV_ID INTEGER NOT NULL,
            HOST VARCHAR(255) NOT NULL,
            HTTP_CONTEXT VARCHAR(255) NULL,
            HTTP_PORT VARCHAR(5) NOT NULL,
            HTTPS_PORT VARCHAR(5) NOT NULL,
            WS_PORT VARCHAR(5) NOT NULL,
            WSS_PORT VARCHAR(5) NOT NULL,
            FOREIGN KEY (GATEWAY_ENV_ID) REFERENCES AM_GATEWAY_ENVIRONMENT(ID) ON DELETE CASCADE,
            PRIMARY KEY (GATEWAY_ENV_ID, HOST))
/

-- Service Catalog --
CREATE TABLE AM_SERVICE_CATALOG (
            UUID VARCHAR(36) NOT NULL,
            SERVICE_KEY VARCHAR(512) NOT NULL,
            MD5 VARCHAR(100) NOT NULL,
            SERVICE_NAME VARCHAR(255) NOT NULL,
            SERVICE_VERSION VARCHAR(30) NOT NULL,
            SERVICE_URL VARCHAR(2048) NOT NULL,
            TENANT_ID INTEGER NOT NULL,
            DEFINITION_TYPE VARCHAR(20),
            DEFINITION_URL VARCHAR(2048),
            DESCRIPTION VARCHAR(1024),
            SECURITY_TYPE VARCHAR(50),
            MUTUAL_SSL_ENABLED SMALLINT DEFAULT 0,
            CREATED_TIME TIMESTAMP NULL,
            LAST_UPDATED_TIME TIMESTAMP NULL,
            CREATED_BY VARCHAR(255),
            UPDATED_BY VARCHAR(255),
            SERVICE_DEFINITION BLOB NOT NULL,
            PRIMARY KEY (UUID),
            CONSTRAINT SERVICE_KEY_TENANT UNIQUE(SERVICE_KEY, TENANT_ID),
            CONSTRAINT SERVICE_NAME_VERSION_TENANT UNIQUE (SERVICE_NAME, SERVICE_VERSION, TENANT_ID))
/

-- Webhooks --
CREATE TABLE AM_WEBHOOKS_SUBSCRIPTION (
            WH_SUBSCRIPTION_ID INTEGER NOT NULL,
            API_UUID VARCHAR(255) NOT NULL,
            APPLICATION_ID VARCHAR(20) NOT NULL,
            TENANT_DOMAIN VARCHAR(255) NOT NULL,
            HUB_CALLBACK_URL VARCHAR(1024) NOT NULL,
            HUB_TOPIC VARCHAR(255) NOT NULL,
            HUB_SECRET VARCHAR(2048),
            HUB_LEASE_SECONDS INTEGER,
            UPDATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            EXPIRY_AT BIGINT,
            DELIVERED_AT TIMESTAMP NULL,
            DELIVERY_STATE INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (WH_SUBSCRIPTION_ID))
/
CREATE SEQUENCE AM_WEBHOOKS_SUBSCRIPTION_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE TRIGGER AM_WEBHOOKS_SUBSCRIPTION_TRIGGER NO CASCADE BEFORE INSERT ON AM_WEBHOOKS_SUBSCRIPTION
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    SET (NEW.WH_SUBSCRIPTION_ID)
       = (NEXTVAL FOR AM_WEBHOOKS_SUBSCRIPTION_SEQUENCE);

END
/

CREATE TABLE AM_WEBHOOKS_UNSUBSCRIPTION (
            API_UUID VARCHAR(255) NOT NULL,
            APPLICATION_ID VARCHAR(20) NOT NULL,
            TENANT_DOMAIN VARCHAR(255) NOT NULL,
            HUB_CALLBACK_URL VARCHAR(1024) NOT NULL,
            HUB_TOPIC VARCHAR(255) NOT NULL,
            HUB_SECRET VARCHAR(2048),
            HUB_LEASE_SECONDS INTEGER,
            ADDED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
/

CREATE TABLE AM_API_SERVICE_MAPPING (
    API_ID INTEGER NOT NULL,
    SERVICE_KEY VARCHAR(256) NOT NULL,
    MD5 VARCHAR(100),
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (API_ID, SERVICE_KEY),
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE
)
/

CREATE TABLE AM_API_ENVIRONMENT_KEYS
(
    UUID            VARCHAR(45)  NOT NULL,
    ENVIRONMENT_ID  VARCHAR(45)  NOT NULL,
    API_UUID          VARCHAR(256) NOT NULL,
    PROPERTY_CONFIG BLOB DEFAULT NULL,
    UNIQUE (ENVIRONMENT_ID, API_UUID),
    FOREIGN KEY (API_UUID) REFERENCES AM_API (API_UUID) ON DELETE CASCADE,
    PRIMARY KEY (UUID)
)
/

CREATE TABLE AM_OPERATION_POLICY (
    POLICY_UUID VARCHAR(45) NOT NULL,
    POLICY_NAME VARCHAR(300) NOT NULL,
    POLICY_VERSION VARCHAR(45) DEFAULT 'v1',
    DISPLAY_NAME VARCHAR(300) NOT NULL,
    POLICY_DESCRIPTION VARCHAR(1024),
    APPLICABLE_FLOWS VARCHAR(45) NOT NULL,
    GATEWAY_TYPES VARCHAR(45) NOT NULL,
    API_TYPES VARCHAR(45) NOT NULL,
    POLICY_PARAMETERS blob,
    ORGANIZATION VARCHAR(100),
    POLICY_CATEGORY VARCHAR(45) NOT NULL,
    POLICY_MD5 VARCHAR(45) NOT NULL,
    PRIMARY KEY(POLICY_UUID)
)
/

CREATE TABLE AM_OPERATION_POLICY_DEFINITION (
   DEFINITION_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
   POLICY_UUID VARCHAR(45) NOT NULL,
   POLICY_DEFINITION blob NOT NULL,
   GATEWAY_TYPE VARCHAR(20) NOT NULL,
   DEFINITION_MD5 VARCHAR(45) NOT NULL,
   UNIQUE (POLICY_UUID, GATEWAY_TYPE),
   FOREIGN KEY (POLICY_UUID) REFERENCES AM_OPERATION_POLICY(POLICY_UUID) ON DELETE CASCADE,
   PRIMARY KEY(DEFINITION_ID)
)
/

CREATE TABLE AM_COMMON_OPERATION_POLICY (
   COMMON_POLICY_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
   POLICY_UUID VARCHAR(45) NOT NULL,
   FOREIGN KEY (POLICY_UUID) REFERENCES AM_OPERATION_POLICY(POLICY_UUID) ON DELETE CASCADE,
   PRIMARY KEY(COMMON_POLICY_ID)
)
/

CREATE TABLE AM_API_OPERATION_POLICY (
   API_SPECIFIC_POLICY_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
   POLICY_UUID VARCHAR(45) NOT NULL,
   API_UUID VARCHAR(45) NOT NULL,
   REVISION_UUID VARCHAR(45),
   CLONED_POLICY_UUID VARCHAR(45),
   FOREIGN KEY (POLICY_UUID) REFERENCES AM_OPERATION_POLICY(POLICY_UUID) ON DELETE CASCADE,
   PRIMARY KEY(API_SPECIFIC_POLICY_ID)
)
/


CREATE TABLE AM_API_OPERATION_POLICY_MAPPING (
   OPERATION_POLICY_MAPPING_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
   URL_MAPPING_ID INTEGER NOT NULL,
   POLICY_UUID VARCHAR(45) NOT NULL,
   POLICY_ORDER INTEGER NOT NULL,
   DIRECTION VARCHAR(10) NOT NULL,
   PARAMETERS VARCHAR(1024) NOT NULL,
   FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
   FOREIGN KEY (POLICY_UUID) REFERENCES AM_OPERATION_POLICY(POLICY_UUID) ON DELETE CASCADE,
   PRIMARY KEY(OPERATION_POLICY_MAPPING_ID)
)
/

CREATE TABLE AM_API_POLICY_MAPPING (
   API_POLICY_MAPPING_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
   API_UUID VARCHAR(256) NOT NULL,
   REVISION_UUID VARCHAR(45),
   POLICY_UUID VARCHAR(45) NOT NULL,
   POLICY_ORDER INTEGER NOT NULL,
   DIRECTION VARCHAR(10) NOT NULL,
   PARAMETERS VARCHAR(1024) NOT NULL,
   FOREIGN KEY (API_UUID) REFERENCES AM_API(API_UUID) ON DELETE CASCADE,
   FOREIGN KEY (POLICY_UUID) REFERENCES AM_OPERATION_POLICY(POLICY_UUID) ON DELETE CASCADE,
   PRIMARY KEY(API_POLICY_MAPPING_ID)
)
/

CREATE TABLE AM_GATEWAY_POLICY_METADATA (
    GLOBAL_POLICY_MAPPING_UUID VARCHAR(45) NOT NULL,
    DISPLAY_NAME               VARCHAR(255) NULL,
    DESCRIPTION                VARCHAR(1023) NULL,
    ORGANIZATION               VARCHAR(100) NOT NULL,
    PRIMARY KEY (GLOBAL_POLICY_MAPPING_UUID)
)
/

CREATE TABLE AM_GATEWAY_POLICY_MAPPING (
    POLICY_TO_FLOW_INFO_MAPPING_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    GLOBAL_POLICY_MAPPING_UUID     VARCHAR(45)   NOT NULL,
    POLICY_UUID                    VARCHAR(45)   NOT NULL,
    POLICY_ORDER                   INTEGER       NOT NULL,
    DIRECTION                      VARCHAR(10)   NOT NULL,
    PARAMETERS                     VARCHAR(1024) NOT NULL,
    FOREIGN KEY (POLICY_UUID) REFERENCES AM_OPERATION_POLICY (POLICY_UUID) ON DELETE RESTRICT,
    FOREIGN KEY (GLOBAL_POLICY_MAPPING_UUID) REFERENCES AM_GATEWAY_POLICY_METADATA (GLOBAL_POLICY_MAPPING_UUID) ON DELETE CASCADE,
    PRIMARY KEY (POLICY_TO_FLOW_INFO_MAPPING_ID)
)
/

CREATE TABLE AM_GATEWAY_POLICY_DEPLOYMENT (
    GATEWAY_LABEL              VARCHAR(255) NOT NULL,
    GLOBAL_POLICY_MAPPING_UUID VARCHAR(45)  NOT NULL,
    ORGANIZATION               VARCHAR(100) NOT NULL,
    FOREIGN KEY (GLOBAL_POLICY_MAPPING_UUID) REFERENCES AM_GATEWAY_POLICY_METADATA (GLOBAL_POLICY_MAPPING_UUID) ON DELETE RESTRICT,
    PRIMARY KEY (ORGANIZATION,GATEWAY_LABEL)
)
/

CREATE TABLE AM_SYSTEM_CONFIGS
(
  ORGANIZATION     VARCHAR(100)            NOT NULL,
  CONFIG_TYPE      VARCHAR(100)            NOT NULL,
  CONFIGURATION    BLOB                    NOT NULL,
  PRIMARY KEY (ORGANIZATION,CONFIG_TYPE)
)
/


CREATE TABLE AM_CORRELATION_CONFIGS
(
    COMPONENT_NAME  VARCHAR(45)     NOT NULL,
    ENABLED         VARCHAR(45)     NOT NULL,
    PRIMARY KEY (COMPONENT_NAME)
)
/

CREATE TABLE AM_CORRELATION_PROPERTIES
(
    PROPERTY_NAME   VARCHAR(45)     NOT NULL,
    COMPONENT_NAME  VARCHAR(45)     NOT NULL,
    PROPERTY_VALUE  VARCHAR(1023)   NOT NULL,
    PRIMARY KEY (PROPERTY_NAME, COMPONENT_NAME),
    FOREIGN KEY (COMPONENT_NAME) REFERENCES AM_CORRELATION_CONFIGS(COMPONENT_NAME) ON DELETE CASCADE
)
/

CREATE TABLE AM_APP_REVOKED_EVENT
(
    CONSUMER_KEY    VARCHAR(255)    NOT NULL,
    TIME_REVOKED    TIMESTAMP       NOT NULL,
    ORGANIZATION    VARCHAR(100)    NOT NULL,
    PRIMARY KEY (CONSUMER_KEY, ORGANIZATION)
)
/

CREATE TABLE AM_SUBJECT_ENTITY_REVOKED_EVENT
(
    ENTITY_ID      VARCHAR(255)    NOT NULL,
    ENTITY_TYPE    VARCHAR(100)    NOT NULL,
    TIME_REVOKED   TIMESTAMP       NOT NULL,
    ORGANIZATION   VARCHAR(100)    NOT NULL,
    PRIMARY KEY (ENTITY_ID, ENTITY_TYPE, ORGANIZATION)
)
/





-- End of API-MGT Tables --

-- Performance indexes start--
create index IDX_ITS_LMT on IDN_THRIFT_SESSION (LAST_MODIFIED_TIME)/
create index IDX_IOAT_UT on IDN_OAUTH2_ACCESS_TOKEN (USER_TYPE)/
create index IDX_IOAT_TSH_TS on IDN_OAUTH2_ACCESS_TOKEN (TOKEN_SCOPE_HASH,TOKEN_STATE)/
create index IDX_AAI_CTX on AM_API (CONTEXT)/
create index IDX_AAKM_CK on AM_APPLICATION_KEY_MAPPING (CONSUMER_KEY)/
create index IDX_AAUM_AI on AM_API_URL_MAPPING (API_ID)/
create index IDX_AAUM_TT on AM_API_URL_MAPPING (THROTTLING_TIER)/
create index IDX_AATP_DQT on AM_API_THROTTLE_POLICY (DEFAULT_QUOTA_TYPE)/
create index IDX_ACG_QT on AM_CONDITION_GROUP (QUOTA_TYPE)/
create index IDX_APS_QT on AM_POLICY_SUBSCRIPTION (QUOTA_TYPE)/
create index IDX_AS_AITIAI on AM_SUBSCRIPTION (API_ID,TIER_ID,APPLICATION_ID)/
create index IDX_APA_QT on AM_POLICY_APPLICATION (QUOTA_TYPE)/
create index IDX_AA_AT_CB on AM_APPLICATION (APPLICATION_TIER,CREATED_BY)/
